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:
SUBJECT | ORIGIN | MONTHS |
---|---|---|
001 | JAN2012 | 1 |
002 | MAR2012 | 4 |
003 | APR2012 | 3 |
004 | SEP2012 | 9 |
Example Outcome:
SUBJECT | MONTH |
---|---|
001 | JAN2012 |
002 | MAR2012 |
002 | APR2012 |
002 | MAY2012 |
002 | JUN2012 |
003 | APR2012 |
003 | MAY2012 |
003 | JUN2012 |
004 | SEP2012 |
004 | OCT2012 |
004 | NOV2012 |
004 | DEC2012 |
004 | JAN2013 |
004 | FEB2013 |
004 | MAR2013 |
004 | APR2013 |
004 | MAY2013 |
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.