You did not very clearly specify what the output should look like. Here is my stab.
data WORK.DRUG(label='Drug Episode');
infile datalines;
input enrolid:$8. startdate:mmddyy10. enddate:mmddyy10.;
format startdate mmddyy10. enddate mmddyy10.;
datalines;
12344 02/02/2019 03/04/2019
32456 03/18/2019 04/17/2019
21424 04/26/2019 05/26/2019
37625 05/25/2019 06/25/2019
34635 12/28/2019 01/28/2020
11111 12/28/2019 04/13/2020
;
data need;
set work.drug;
m='01Jan2019'd;
do until (m= '01Dec2020'd);
mtext= put(m,monyy7.);
/* month well before start date*/
If intck('month',m,intnx('month',startdate,0,'B')) > 0 then days=0;
/* month the same as the start date*/
else if m=intnx('month',startdate,0,'B') then
days=intck('day',startdate,intnx('month',startdate,0,'E'));
/* end of period in the month*/
else if m=intnx('month',enddate,0,'B') then days= day(enddate);
/* month after the end of the period*/
else if intck('month',m,intnx('month',enddate,0,'E')) < 0 then days=0;
/* month completely within interval*/
else days= day(intnx('month',m,0,'E'));
output;
call missing(days);
m= intnx('month',m,1,'b');
end;
format m monyy.;
run;
proc transpose data=need out=want(drop=_name_)
;
by notsorted enrolid startdate enddate ;
var days;
id mtext;
run;
Your data did not include anything with a start end period longer than a month, or describe the result intended. I added one at the end of the data set so you can see if that is proper result.
Note the comments for the different comparisons as to which each interval is setting the value for the Days variable.
There may be something slicker, especially if your intervals ALWAYS are basically one month and you state that to be the case.
Note that I used 4-digit years. There is NO good that comes from using two digit years. Also made all of the variable names the same length.
It may be that "wide" data really isn't needed. How do you expect to use the resulting set? Normally placing actual data, the month/year, into a Variable is poor practice. Also sorts of the variable names result in awkward behavior.
... View more
