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

Solved
Occasional Contributor
Posts: 5

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

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)

Accepted Solutions
Solution
‎02-10-2016 04:26 PM
Super User
Posts: 11,810

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

[ Edited ]

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;
``````

All Replies
Solution
‎02-10-2016 04:26 PM
Super User
Posts: 11,810

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

[ Edited ]

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;
``````

Occasional Contributor
Posts: 5

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

Yaaaaaaaaaaaay it worked. Thank you!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
• 2 replies
• 327 views
• 1 like
• 2 in conversation