DATA Step, Macro, Functions and more

New variable based off dates and order

Reply
Contributor
Posts: 55

New variable based off dates and order

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! 

Super User
Posts: 11,343

Re: New variable based off dates and order

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;
Super User
Posts: 10,047

Re: New variable based off dates and order

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;

Super User
Posts: 11,343

Re: New variable based off dates and order

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

Super User
Posts: 10,047

Re: New variable based off dates and order

Opps. I am afraid SAS can't those words : First , Second ......
Ask a Question
Discussion stats
  • 4 replies
  • 256 views
  • 0 likes
  • 3 in conversation