Group by patients based on the condition

[Sathish_jammy]

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.

[ed_sas_member]

Hi @Sathish_jammy 

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;

[yabwon · Accepted solution]

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