BookmarkSubscribeRSS Feed
hwangnyc
Quartz | Level 8

Hi Everyone,

 

I'd like to create a variable based off a group of dates and order.

 

Please see my desired output below: 

 

ChildIDAsthmaEventDate_HWSession
196-May-08First Session
196-May-08First Session
196-May-08First Session
196-May-08First Session
196-Jun-08Second Session
1922-Dec-08Third Session
1922-Dec-08Third Session
19447-Mar-14First Session
19447-Mar-14First Session
19447-Mar-14First Session
19447-Mar-14First Session
19447-Mar-14First Session
19447-Mar-14First Session
19447-Mar-14First Session
194411-Jul-14Second Session
194411-Jul-14Second Session
194411-Jul-14Second Session
194411-Jul-14Second Session
194411-Jul-14Second Session
194411-Jul-14Second Session
194411-Jul-14Second Session
19446-Nov-14Second Session
19446-Nov-14Third Session
19446-Nov-14Third Session
19446-Nov-14Third Session
19446-Nov-14Third Session
19446-Nov-14Third Session
19446-Nov-14Third Session
19443-Apr-15Fourth Session
19443-Apr-15Fourth Session
19443-Apr-15Fourth Session
19443-Apr-15Fourth Session
19443-Apr-15Fourth Session
19443-Apr-15Fourth Session
19443-Apr-15Fourth Session
194424-Dec-15Fifth Session
194424-Dec-15Fifth Session
194424-Dec-15Fifth Session
194424-Dec-15Fifth Session
194424-Dec-15Fifth Session
194424-Dec-15Fifth Session
194424-Dec-15Fifth 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! 

4 REPLIES 4
ballardw
Super User

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;
Ksharp
Super User
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;

ballardw
Super User

Wont the Words format generate One, Two and Three instead of First, Second and Third?

Ksharp
Super User
Opps. I am afraid SAS can't those words : First , Second ......

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 2117 views
  • 0 likes
  • 3 in conversation