topic Re: New variable based off dates and order in SAS Programming

New variable based off dates and order

hwangnyc — Tue, 16 Aug 2016 19:37:17 GMT

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!

Re: New variable based off dates and order

ballardw — Tue, 16 Aug 2016 20:04:32 GMT

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;

Re: New variable based off dates and order

Ksharp — Wed, 17 Aug 2016 01:40:12 GMT

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;

Re: New variable based off dates and order

ballardw — Wed, 17 Aug 2016 05:44:31 GMT

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

Re: New variable based off dates and order

Ksharp — Wed, 17 Aug 2016 09:53:17 GMT

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