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?
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.