Hello All. I have data that looks like the following:
PtID EnrollDate CloseDate
1 12/31/2012 4/20/2013
2 1/5/2011 6/25/2011
3 3/9/2011 3/20/2012
.
.
.
.
I would like to have data that looks like this:
PtID Month1 month2 month3 month4 month5 month6 month7 month8 month9 month10 month11 month12
1 12/2012 1/2013 2/2013 3/2013 4/2013 . . . . . . .
2 1/2011 2/2011 3/2011 4/2011 5/2011 6/2011 . . . . . .
3 3/2011 4/2011 5/2011 6/2011 7/2011 8/2011 9/2011 10/2011 12/2011 1/2012 2/2012 3/2012
Can you help?
Thanks!
Using HaiKuo's data.
data have; input PtID (EnrollDate CloseDate) (:mmddyy10.); cards; 1 12/31/2012 4/20/2013 2 1/5/2011 6/25/2011 3 3/9/2011 3/20/2012 ; run; data temp; set have; length month $ 8; do i=enrolldate to closedate; if month(i) ne month(_i) then do; month=catx('/',month(i),year(i)); output;end; _i=i; end; keep ptid month; run; data temp; set temp; if ptid ne lag(ptid) then n=0; n+1; run; proc transpose data=temp out=want prefix=month; by ptid; id n; var month; run;
Ksharp
data have;
input PtID (EnrollDate CloseDate) (:mmddyy10.);
cards;
1 12/31/2012 4/20/2013
2 1/5/2011 6/25/2011
3 3/9/2011 3/20/2012
;
proc sql noprint;
select cats(max(intck('month', EnrollDate,CloseDate))+1) into :month from have; quit;
data want;
set have;
array mon(&month.) month1-month&month.;
format month1-month&month. monyy7.;
do i=1 to intck('month', EnrollDate,CloseDate)+1;
mon(i)=intnx('month',EnrollDate,i-1);
end;
keep ptid month:;
run;
Haikuo
Using HaiKuo's data.
data have; input PtID (EnrollDate CloseDate) (:mmddyy10.); cards; 1 12/31/2012 4/20/2013 2 1/5/2011 6/25/2011 3 3/9/2011 3/20/2012 ; run; data temp; set have; length month $ 8; do i=enrolldate to closedate; if month(i) ne month(_i) then do; month=catx('/',month(i),year(i)); output;end; _i=i; end; keep ptid month; run; data temp; set temp; if ptid ne lag(ptid) then n=0; n+1; run; proc transpose data=temp out=want prefix=month; by ptid; id n; var month; run;
Ksharp
Thank you both for your replies. However I am getting error messages that I am unable to figure out.
HaiKuo's error message is the following:
NOTE: Invalid (or missing) arguments to the INTCK function have caused the function to return a missing value.
ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing,
zero, or invalid.
and Ksharp's error message is:
ERROR: Invalid DO loop control information, either the INITIAL or TO expression is missing or the BY expression is missing, zero, or invalid.
Which means enrolldate and closedate are the real variable name in your table ?
I changed the variable names, and the data set names to my actual data. I found that the reason it was erroring out was because some patients do not have a closing date. After fixing those missing values, the program runs.
Thanks a bunch!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.