topic Re: How to populate a group of variables with the non missing value for all the group in SAS Programming

How to populate a group of variables with the non missing value for all the group

sbopster — Fri, 21 May 2021 22:17:36 GMT

Input data: group flag 1 . 1 . 1 1 1 . 2 . 2 1 2 . 3 . 3 1 3 . 3 1 3 . 3 . ; I want the following output so for every group, if there is a flag=1 then all the flag_new should be a 1 for everyone in that group. If missing then all the values should be 0 Output desired: group flag new_flag 1 . 1 1 . 1 1 1 1 1 . 1 2 . 0 2 . 0 2 . 0 3 . 1 3 1 1 3 . 1 3 1 1 3 . 1 3 . 1

Re: How to populate a group of variables with the non missing value for all the group

PaigeMiller — Fri, 21 May 2021 22:20:23 GMT

Why is the output for group 2 a zero when there was one non-missing in the input?

PS: Don't type in a code box if you are not going to show us code.

Re: How to populate a group of variables with the non missing value for all the group

sbopster — Fri, 21 May 2021 22:41:49 GMT

if I knew how to do it or had code I would type it in. I mean how else are you supposed to ask a question? thank you for your response.

Re: How to populate a group of variables with the non missing value for all the group

PaigeMiller — Fri, 21 May 2021 22:47:08 GMT

You just did it. You typed your comment.

But why does group 2 have a zero in the output but a non-missing in the input?

Re: How to populate a group of variables with the non missing value for all the group

mkeintz — Sat, 22 May 2021 01:30:10 GMT

data want;
  merge have   have (keep=group flag where=(flag^=.));
  by group;
  if  flag=. then flag=0;
run;

This works as expected if (1) data are sorted by group, (2) no group has any variation in non-missing values of flag.

Re: How to populate a group of variables with the non missing value for all the group

Ksharp — Sat, 22 May 2021 11:43:07 GMT

data have;
input group flag;
cards;
1 . 
1 . 
1 1 
1 .
2 . 
2 . 
2 . 
3 .
3 1
3 .
3 1
3 .
3 .
;

proc sql;
create table want as
select *,max(flag)=1 as new_flag
 from have 
  group by group;
quit;