DATA Step, Macro, Functions and more

Adding Varying Number of Rows

Accepted Solution Solved
Reply
Regular Contributor
Posts: 220
Accepted Solution

Adding Varying Number of Rows

Sorry for the vague title.  I couldn't think of a way to summarize my task.

I have a dataset that has a subject ID with a date that they came on the study (in MONYY7. format) and the number of months that they were on the study.  I want to create a long, vertical table that has one entry for every month they were on study.  I cannot picture an "easy" method to do this.  I can only imagine storing macro variables with distinct subject IDs and number of months then looping through all subjects and number of months.  I have 321 subjects, and the months go up to 21, so looping just doesn't seem as efficient as I would like.  Are there any ideas?  I was wondering if PROC TRANSPOSE would have such capabilities.tend a D

Example Dataset:

SUBJECTORIGINMONTHS
001JAN20121
002MAR20124
003APR20123
004SEP20129

Example Outcome:

SUBJECTMONTH
001JAN2012
002MAR2012
002APR2012
002MAY2012
002JUN2012
003APR2012
003MAY2012
003JUN2012
004SEP2012
004OCT2012
004NOV2012
004DEC2012
004JAN2013
004FEB2013
004MAR2013
004APR2013
004MAY2013

Accepted Solutions
Solution
‎01-23-2015 11:27 AM
Respected Advisor
Posts: 3,124

Re: Adding Varying Number of Rows

data have;

     input SUBJECT$  ORIGIN:monyy7.  MONTHS;

     format origin monyy7.;

     cards;

001 JAN2012 1

002 MAR2012 4

003 APR2012 3

004 SEP2012 9

;

data want;

     set have;

     format month monyy7.;

     do n=0 by 1 to months-1;

           month=intnx('month', origin,n);

           output;

     end;

     keep subject month;

run;

View solution in original post


All Replies
Solution
‎01-23-2015 11:27 AM
Respected Advisor
Posts: 3,124

Re: Adding Varying Number of Rows

data have;

     input SUBJECT$  ORIGIN:monyy7.  MONTHS;

     format origin monyy7.;

     cards;

001 JAN2012 1

002 MAR2012 4

003 APR2012 3

004 SEP2012 9

;

data want;

     set have;

     format month monyy7.;

     do n=0 by 1 to months-1;

           month=intnx('month', origin,n);

           output;

     end;

     keep subject month;

run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 1 reply
  • 151 views
  • 0 likes
  • 2 in conversation