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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.