Hi folks!
I am trying to manipulate data to split into multiple rows, one per month/year, and I'm very new to writing arrays in SAS. I've posted an example of my data below. Currently, it's organized as one row per "visit." There is a unique id for each person, an "in" date (the day their visit began), an "out" date (the day they left), and a special indicator ("ind") which denotes whether they had another visit within the previous 2 years.
I need to do two things: (1) split the records into one row per person, per visit, per month, and (2) retain the "ind" for each record.
One extra quirk: If their visit "out" date is on the first day of any given month, I do not want a row for that month (if they left on the first day of the month then their stay did not span into that month).
Hope I explained well! I tried my best 🙂
What my data looks like now:
Obs | id | in | out | ind |
1 | 1111 | 10/18/2010 | 10/28/2010 | . |
2 | 1111 | 1/17/2011 | 1/24/2011 | 1 |
3 | 2222 | 7/9/2011 | 10/9/2011 | . |
4 | 3333 | 4/7/2010 | 4/19/2010 | . |
5 | 3333 | 1/10/2011 | 2/24/2011 | 1 |
6 | 4444 | 8/31/2009 | 9/17/2009 | . |
7 | 5555 | 3/31/2009 | 5/1/2009 | . |
What I'm trying to do:
Obs | id | month | year | ind |
1 | 1111 | 10 | 2010 | . |
2 | 1111 | 1 | 2011 | 1 |
3 | 2222 | 7 | 2011 | . |
4 | 2222 | 8 | 2011 | . |
5 | 2222 | 9 | 2011 | . |
6 | 2222 | 10 | 2011 | . |
7 | 3333 | 4 | 2010 | . |
8 | 3333 | 1 | 2011 | 1 |
9 | 3333 | 2 | 2011 | 1 |
10 | 4444 | 8 | 2009 | . |
11 | 4444 | 9 | 2009 | . |
12 | 5555 | 3 | 2009 | . |
13 | 5555 | 4 | 2009 | . |
(note there is no record for 5555 month=5; because they left on the 1st of the month)
Assuming In and Out are SAS date values and not strings I think this works:
data want;
set have;
date=in;
if in lt out then do while (date lt out) ;
month= month(date);
year = year (date);
output;
date = intnx('month',date,1);
end;
else put "WARNING: Out is before In for ID: " ID;
drop date in out;
run;
Assuming In and Out are SAS date values and not strings I think this works:
data want;
set have;
date=in;
if in lt out then do while (date lt out) ;
month= month(date);
year = year (date);
output;
date = intnx('month',date,1);
end;
else put "WARNING: Out is before In for ID: " ID;
drop date in out;
run;
Yaaaaaaaaaaaay it worked. Thank you!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.