Indicating ALL months a patient was in the program

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Indicating ALL months a patient was in the program

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!



Accepted Solutions
Solution
‎04-26-2013 03:51 AM
Super User
Posts: 9,681

Re: Indicating ALL months a patient was in the program

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: Indicating ALL months a patient was in the program

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

Solution
‎04-26-2013 03:51 AM
Super User
Posts: 9,681

Re: Indicating ALL months a patient was in the program

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

Contributor
Posts: 30

Re: Indicating ALL months a patient was in the program

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.

Super User
Posts: 9,681

Re: Indicating ALL months a patient was in the program

Which means enrolldate and closedate are the real variable name in your table ?

Contributor
Posts: 30

Re: Indicating ALL months a patient was in the program

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!


☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 367 views
  • 6 likes
  • 3 in conversation