Hi Everyone,
I'd like to create a variable based off a group of dates and order.
Please see my desired output below:
ChildID | AsthmaEventDate_HW | Session |
19 | 6-May-08 | First Session |
19 | 6-May-08 | First Session |
19 | 6-May-08 | First Session |
19 | 6-May-08 | First Session |
19 | 6-Jun-08 | Second Session |
19 | 22-Dec-08 | Third Session |
19 | 22-Dec-08 | Third Session |
1944 | 7-Mar-14 | First Session |
1944 | 7-Mar-14 | First Session |
1944 | 7-Mar-14 | First Session |
1944 | 7-Mar-14 | First Session |
1944 | 7-Mar-14 | First Session |
1944 | 7-Mar-14 | First Session |
1944 | 7-Mar-14 | First Session |
1944 | 11-Jul-14 | Second Session |
1944 | 11-Jul-14 | Second Session |
1944 | 11-Jul-14 | Second Session |
1944 | 11-Jul-14 | Second Session |
1944 | 11-Jul-14 | Second Session |
1944 | 11-Jul-14 | Second Session |
1944 | 11-Jul-14 | Second Session |
1944 | 6-Nov-14 | Second Session |
1944 | 6-Nov-14 | Third Session |
1944 | 6-Nov-14 | Third Session |
1944 | 6-Nov-14 | Third Session |
1944 | 6-Nov-14 | Third Session |
1944 | 6-Nov-14 | Third Session |
1944 | 6-Nov-14 | Third Session |
1944 | 3-Apr-15 | Fourth Session |
1944 | 3-Apr-15 | Fourth Session |
1944 | 3-Apr-15 | Fourth Session |
1944 | 3-Apr-15 | Fourth Session |
1944 | 3-Apr-15 | Fourth Session |
1944 | 3-Apr-15 | Fourth Session |
1944 | 3-Apr-15 | Fourth Session |
1944 | 24-Dec-15 | Fifth Session |
1944 | 24-Dec-15 | Fifth Session |
1944 | 24-Dec-15 | Fifth Session |
1944 | 24-Dec-15 | Fifth Session |
1944 | 24-Dec-15 | Fifth Session |
1944 | 24-Dec-15 | Fifth Session |
1944 | 24-Dec-15 | Fifth Session |
Notice the oldest dates would be the first session the next group of dates would be the second session and so forth. A single row can be considered a group. Any suggestions would be greatly appreciated!
Assuming your data is sorted by ChildID and that date as it appears this will give you a session number. I am not going to bother to create text like "First".
data want;
set have;
by ChildID AsthmaEventDate_HW;
retain SessionNumber;
if first.ChildID then SessionNumber=1;
else if first.AsthmaEventDate_HW then SessionNumber+1;
run;
data have; infile cards expandtabs truncover; input Id (sales Datesold) (:$40.); cards; 1 car 1/1/2001 1 car 1/1/2001 1 truck 1/3/2001 2 motorcycle 1/5/2001 2 truck 1/8/2001 3 bike 1/4/2003 3 motorcycle 1/5/2003 3 truck 1/6/2003 3 bike 1/6/2003 ; run; data want; set have; by id Datesold; if first.id then n=0; n+first.Datesold; length session $ 80; session=catx(' ',put(n,words.),'session'); drop n; run;
Wont the Words format generate One, Two and Three instead of First, Second and Third?
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.