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;
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;
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;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: