DATA Step, Macro, Functions and more

Reminder by Group

Accepted Solution Solved
Reply
Regular Contributor
Posts: 184
Accepted Solution

Reminder by Group

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;

Accepted Solutions
Solution
‎11-16-2016 02:00 PM
Trusted Advisor
Posts: 1,586

Re: Reminder by Group

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;
     
     
             
     
     
     
     

View solution in original post


All Replies
Solution
‎11-16-2016 02:00 PM
Trusted Advisor
Posts: 1,586

Re: Reminder by Group

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;
     
     
             
     
     
     
     
Super User
Posts: 11,343

Re: Reminder by Group

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;
Regular Contributor
Posts: 184

Re: Reminder by Group

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 280 views
  • 2 likes
  • 3 in conversation