topic Re: Reminder by Group in SAS Programming

Reminder by Group

Ujjawal — Wed, 16 Nov 2016 17:37:12 GMT

Hi Everyone,

I have data in the following format (First 2 columns). The third column 'STATUS' is my desired output. I want to generate serial numbers based on variable 'Flag' by a grouping variable 'LOC'. If flag is equal to 1, SAS should create a variable 'Status' which contains same serial number of every three rows. For example, for a group LOC =AA , the first 3 rows have value 'C1' in the table below and the next row has value 'C2'. Had it contain two more values, the next 2 rows would have a value 'C2' and so on (i.e. 'C3', 'C4'....), If Flag = 0, it should create serial number of every four rows (instead of 3).

INPUT		OUTPUT
LOC	FLAG	STATUS
AA	1	C1
AA	1	C1
AA	1	C1
AA	1	C2
AA	0	D1
AA	0	D1
AA	0	D1
AA	0	D1
AB	1	C1
AB	1	C1
AB	1	C1
AB	1	C2
AB	1	C2
AB	0	D1
AB	0	D1
AB	0	D1
AB	0	D1

The below is the sample SAS code for creating this dataset. It's just the small sample of the large data i have in the same format.

data temp;
input loc $ flag;
cards;
AA 1
AA 1
AA 1
AA 1
AA 0
AA 0
AA 0
AA 0
AA 1
AB 1
AB 1
AB 1
AB 0
AB 0
AB 0
AB 0
;
run;

Re: Reminder by Group

Shmuel — Wed, 16 Nov 2016 18:16:23 GMT

Did you change your sample from your first demo of what you want ?

anyhow, I have run next code and it gives output exactly as wanted:

data temp;
input loc $ flag;
cards;
AA 1
AA 1
AA 1
AA 1
AA 0
AA 0
AA 0
AA 0
AB 1
AB 1
AB 1
AB 1
AB 1
AB 0
AB 0
AB 0
AB 0
;
run;

data want;
 set temp;
  by loc notsorted flag;
     retain count count1;
     if first.flag then do; count=0; count1=1; end;
     if flag=0 then link flagz; else
     if flag=1 then link flag1; else
     put '** Flag error on line ' _N_  flag=;
     drop count count1;
return;
flag1:
     count+1;
     if mod(count,4)=0 then count1+1;
     status = 'C'||left(count1);
return;
flagz:
     count+1;
     if mod(count,5)=0 then count1+1;
     status = 'D'||left(count1);
return;  
run;

Re: Reminder by Group

ballardw — Wed, 16 Nov 2016 18:17:40 GMT

Assumes the data is sorted by LOC;

data want;
   set temp;
   by loc;
   retain rowcount;
   length status $ 4.;
   if first.loc then rowcount=0;
   if flag then do;
      rowcount+1;
      count = ceil(rowcount/3);
      status = cats("C",count);
   end;
   else Status = "D1";
   drop rowcount count;
run;

Re: Reminder by Group

Ujjawal — Wed, 16 Nov 2016 18:59:57 GMT

Thanks @ballardw for your elegant solution. I just minor tweaked it for my requirement.

data want;
   set temp;
   by loc;
   retain rowcount;
   length status $ 4.;
   if first.loc then rowcount=0;
   if flag then do;
      rowcount+1;
      count = ceil(rowcount/3);
      status = cats("C",count);
	   rowcount=0;
   end;
   else do;
   rowcount+1;
   count = ceil(rowcount/4);
   status = cats("D",count);
   end;
   drop rowcount count;
run;