Dear Experts,
I need to fetch the IDs from my dataset based on group by condition.
I'll share my sample data for your reference.
data have;
input ID Des1 Des2 Des3 Des4;
cards;
123 1 0 0 0
123 1 0 0 0
123 1 0 0 0
159 1 0 0 0
159 1 1 0 0
159 0 1 0 0
321 1 0 0 0
456 0 0 0 1
456 1 0 0 0
456 1 0 0 0
;
I expected output as IDs who are Deceased (Des1 =1) first and the rest of Des_ = 0. (Output - 123, 321)
Kindly let me know for any clarification.
Hi @Sathish_jammy ,
Like this:
data have;
input ID Des1 Des2 Des3 Des4;
cards;
123 1 0 0 0
123 1 0 0 0
123 1 0 0 0
159 1 0 0 0
159 1 1 0 0
159 0 1 0 0
321 1 0 0 0
456 0 0 0 1
456 1 0 0 0
456 1 0 0 0
;
run;
data want;
set have;
by id;
if first.id then elig = 1; retain elig;
cond = Des1=1 and sum(Des2, Des3, Des4) = 0 ;
elig = elig * cond;
if last.id and elig then output;
run;
?
All the best
Bart
Like this?
proc means noprint;
var Des1 Des2 Des3 Des4;
class id;
ways 1;
output out=have_stat (drop=_:) n= sum= /autoname;
run;
data have_flag;
set have_stat;
flag_des1 = 0;
if Des1_N = Des1_Sum then flag_des1=1; /* Identifies id 123 and 321 for example*/
/*you can compute the flag for Des2, ... also */
run;
proc sql;
create table want as
select a.*, b.flag_des1
from have as a inner join have_flag as b
on a.id=b.id
order by b.flag_des1 desc, a.id;
quit;
Hi @Sathish_jammy ,
Like this:
data have;
input ID Des1 Des2 Des3 Des4;
cards;
123 1 0 0 0
123 1 0 0 0
123 1 0 0 0
159 1 0 0 0
159 1 1 0 0
159 0 1 0 0
321 1 0 0 0
456 0 0 0 1
456 1 0 0 0
456 1 0 0 0
;
run;
data want;
set have;
by id;
if first.id then elig = 1; retain elig;
cond = Des1=1 and sum(Des2, Des3, Des4) = 0 ;
elig = elig * cond;
if last.id and elig then output;
run;
?
All the best
Bart
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.