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;
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;
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;
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;
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.