Search code examples
dataframeif-statementgroup-bysas

How to make a dataframe based on the condition of one of many answers for the same ID?

Let's say I have this dataset I created


data df; 
input ID status$; 
datalines; 
1 YES
1 YES
1 YES
2 NO
2 YES
2 YES
2 YES
2 YES
3 NO
3 NO
3 NO
;
run;

What I wish is the output as follows with the condition : for the same ID, if one status hasat least one answer as NO then his status is NO, and if all the answers are yes then his status is yes. I want a programmation to automatise the results.

I tried using proc transpose but I think there is something more efficient.

The output desired would be the results of the following programming.


data df; 
input ID status$; 
datalines; 
1 YES
2 NO
3 NO
;
run;
Solution

  • Try this

    data df; 
input ID status$; 
datalines; 
1 YES 
1 YES 
1 YES 
2 NO  
2 YES 
2 YES 
2 YES 
2 YES 
3 NO  
3 NO  
3 NO  
;

proc sql;
   create table want as
   select ID, min(status) as status
   from df
   group by ID
   ;
quit;