How to split record into multiple rows (one row per month/year)

[he_ko]

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)

[ballardw · Accepted solution]

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;

[he_ko]

Yaaaaaaaaaaaay it worked. Thank you!