topic Re: Flag missing across all rows in SAS Programming

Flag missing across all rows

Cruise — Tue, 05 Feb 2019 21:59:28 GMT

Hi folks,

I have a data with missing and I'd like to flag those individuals with missing data across all the rows as shown in the snippet below.

Any suggestions appreciated. Thanks in advance.

DATA HAVE;
INPUT ID DIAGNOSIS FLAG;
CARDS;  
1 3  0 
1 1  0
1 99 0
1 99 0
1 99 0
1 99 0
1 1  0
1 99 0
1 3  0
1 99 0
2 99 1
2 99 1
2 99 1
2 99 1
2 99 1
;
PROC PRINT; RUN;

Re: Flag missing across all rows

PeterClemmensen — Tue, 05 Feb 2019 22:03:27 GMT

So 99 is indicating 'missing' here?

Re: Flag missing across all rows

Cruise — Tue, 05 Feb 2019 22:03:52 GMT

YES

Re: Flag missing across all rows

PeterClemmensen — Tue, 05 Feb 2019 22:10:49 GMT

You can do something like this

data have;
input id diagnosis;
cards;  
1 3 
1 1 
1 99
1 99
1 99
1 99
1 1 
1 99
1 3 
1 99
2 99
2 99
2 99
2 99
2 99
;

data want(drop=c);
   c=0;
   do _n_ = 1 by 1 until (last.id);
      set have;
      by id;
      if diagnosis=99 then c=c+1;
   end;

   do until (last.id);
      set have;
      by id;
      flag=ifn(c=_N_, 1, 0);
      output;
   end;

run;

Re: Flag missing across all rows

novinosrin — Tue, 05 Feb 2019 22:12:40 GMT


DATA HAVE;
INPUT ID DIAGNOSIS;* FLAG;
CARDS;  
1 3  0 
1 1  0
1 99 0
1 99 0
1 99 0
1 99 0
1 1  0
1 99 0
1 3  0
1 99 0
2 99 1
2 99 1
2 99 1
2 99 1
2 99 1
;

proc sql;
create table want as
select *,sum(DIAGNOSIS=99)=n(id) as flag
from have 
group by id;
quit;

Re: Flag missing across all rows

Cruise — Tue, 05 Feb 2019 22:20:30 GMT

Thanks both of you. Excellent solutions.