Hi all,
I am trying to make a variable 'B' that will code each of the lines of A by group number. I want it to code B=1 for the first group of the first day when when A=1, B=2 for the second group of that day when A=1, etc. I need this counter to restart the next day (and by next participant). Any ideas on how to get this to work? Thank you!
Data I have:
ID Day A
1 1 1
1 1 1
1 1 .
1 1 1
1 1 1
1 1 1
1 1 .
1 2 .
1 2 1
1 2 1
1 2 .
1 2 1
1 2 .
2 1 1
2 1 1
2 1 1
2 1 .
2 1 .
2 1 1
2 1 1
2 2 1
2 2 .
2 2 1
Data I want:
ID Day A B
1 1 1 1
1 1 1 1
1 1 . .
1 1 1 2
1 1 1 2
1 1 1 2
1 1 . .
1 2 . .
1 2 1 1
1 2 1 1
1 2 . .
1 2 1 2
1 2 . .
2 1 1 1
2 1 1 1
2 1 1 1
2 1 . .
2 1 . .
2 1 1 2
2 1 1 2
2 2 1 1
2 2 . .
2 2 1 2
This works for your example. Sort order might be an issue with more complex data.
/* to make a data set of example data to test*/
data have;
input id day a;
datalines;
1 1 1
1 1 1
1 1 .
1 1 1
1 1 1
1 1 1
1 1 .
1 2 .
1 2 1
1 2 1
1 2 .
1 2 1
1 2 .
2 1 1
2 1 1
2 1 1
2 1 .
2 1 .
2 1 1
2 1 1
2 2 1
2 2 .
2 2 1
;
run;
data want;
set have;
by id day notsorted A;
retain group;
drop group ;
if first.day then group=0;
if first.a and a>0 then group+1;
if not missing(A) then b=group;
run;
This works for your example. Sort order might be an issue with more complex data.
/* to make a data set of example data to test*/
data have;
input id day a;
datalines;
1 1 1
1 1 1
1 1 .
1 1 1
1 1 1
1 1 1
1 1 .
1 2 .
1 2 1
1 2 1
1 2 .
1 2 1
1 2 .
2 1 1
2 1 1
2 1 1
2 1 .
2 1 .
2 1 1
2 1 1
2 2 1
2 2 .
2 2 1
;
run;
data want;
set have;
by id day notsorted A;
retain group;
drop group ;
if first.day then group=0;
if first.a and a>0 then group+1;
if not missing(A) then b=group;
run;
This works perfectly, thank you!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.