Count days in month arrays

[dat333]

Hi ,

I have a span file and want to create month array in which each month counts the number of days a client stayed in hospital.

Thanks in advance !

Here is the sample file:

data myfile1;
input clientID $ (startDt EndDt)(:date9.);
format startDt EndDt date9.;
cards;
1111 03Oct2011 21Nov2011
1111 10Dec2011 11Jan2012
2222 15Jan2012 21MAR2012
2222 03May2012 24Jun2012
3333 03Nov2011 21Dec2011
;
run;

I want the outcome file like this:  DayCount1 represents the earliest month in the StartDt, and DayCount9 represents the last month of 

the EndDt.

%let FirstMo= %sysfunc(intck(month,"01Sep2011"d,"01Oct2011"d));
%let LastMo= %sysfunc(intck(month,"01Oct2011"d,"01Jul2012"d));

%put &FirstMo;
%put &LastMo;

clientID	DayCount1	DayCount2	DayCount3	DayCount4	DayCount5	DayCount6	DayCount7	DayCount8	DayCount9
1111	28	21	21	11	0	0	0	0	0
2222	0	0	0	16	29	21	0	28	24
3333	0	27	21	0	0	0	0	0	0

The follow code create indicator array, but I need array that count # of days in each month clients stayed .

data myfile2;
set myfile1;
by clientID;

Adm_Mo= intck('month',"01Oct2011"d,startDt)+1;
Rel_Mo= intck('month',"01Oct2011"d,EndDt)+1;
retain flg&FirstMo. - flg&LastMo.;
array flg[&FirstMo.:&LastMo.] flg&FirstMo. - flg&LastMo.;

if first.clientID then do i = &FirstMo. to &LastMo.;
flg[i] = 0;
end;
do i = &FirstMo. to &LastMo.;
if i GE Adm_Mo and i LE Rel_Mo then flg[i] = 1;
end;

if last.clientID then output;
keep clientID flg&FirstMo. - flg&LastMo.;
run;

[novinosrin · Accepted solution]

Hi @dat333   Your very wide reporting structure doesn't make it squeaky clean for me atleast as each Daycount is meant for monthly admission. At any rate, it's ideal to have long and narrow and take a call in how best the summary report is to be presented. 

The first step is basically to establish the day count. To my mind 03oct2011 to 31oct2011 is 29 days and your output shows 28 days. Well, this is something you can adjust being a simple arithmetic. 

data myfile1;
input clientID $ (startDt EndDt)(:date9.);
format startDt EndDt date9.;
cards;
1111 03Oct2011 21Nov2011
1111 10Dec2011 11Jan2012
2222 15Jan2012 21MAR2012
2222 03May2012 24Jun2012
3333 03Nov2011 21Dec2011
;
run;




data temp;
set myfile1;
by clientid;
do  while(startdt<enddt);
 ed=intnx('mon',startDt	,0,'e');
 if put(ed,monyy7. -l)=put(enddt,monyy7. -l) then ed=enddt;
 DayCount=ed-startdt+1;
 month_yr=put(ed,monyy7.);
 output;
 startdt= ed+1;
end;
format ed date9.;
keep clientid daycount month_yr;
run;

The above is the fundamental structure that needs to be considered essentially forming the base to go wide if you like. I leave that up to you to decide how you may wanna transpose it as it doesnt provide a comprehensive structure to have it wide month after month, though you can transpose like below

proc transpose data=temp out=want(drop=_:) prefix=Daycount_;
by clientid ;
id month_yr;
var daycount ;
run;

[dat333]

Thanks! That's exactly what I needed.

[unison]

A few ideas here:

data myfile1;
input clientID $ (startDt EndDt)(:date9.);
format startDt EndDt date9.;
cards;
1111 03Oct2011 21Nov2011
1111 10Dec2011 11Jan2012
2222 15Jan2012 21MAR2012
2222 03May2012 24Jun2012
3333 03Nov2011 21Dec2011
;
run;

data step1;
set myfile1;
do while (startdt lt enddt);
	thismonth = put(startdt,yymmn6.);
	days = intck('day',startdt,min(intnx('month',startdt,0,'e'),enddt))+1;
	output;
	startdt = intnx('month',startdt,1,'b');
end;
drop enddt;
run;

proc sort data=step1;
by clientid thismonth;
run;

proc transpose data=step1 out=want(drop=_name_) prefix=month_;
by clientid;
id thismonth;
var days;
run;

Or if you are concerned about missing values in want:

(must have SAS ETS for proc timeseries)

proc timeseries data=step1 out=step2;
by clientid;
id startdt interval=month start='01OCT2011'd end='30JUN2012'd setmissing=0;
var days;
run;

proc transpose data=step2 out=want(drop=_name_) prefix=month_;
by clientid;
id startdt;
var days;
run;

[dat333]

Thanks for your help!