Programming the statistical procedures from SAS

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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:

Obsidinoutind
1111110/18/201010/28/2010.
211111/17/20111/24/20111
322227/9/201110/9/2011.
433334/7/20104/19/2010.
533331/10/20112/24/20111
644448/31/20099/17/2009.
755553/31/20095/1/2009.

 

What I'm trying to do:

Obsidmonthyearind
11111102010.
21111120111
3222272011.
4222282011.
5222292011.
62222102011.
7333342010.
83333120111
93333220111
10444482009.
11444492009.
12555532009.
13555542009.

(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: 10,880

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;

 

View solution in original post


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

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! Woman Very Happy

☑ This topic is solved.

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

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