Hi. I have a set of data where I need to split out the number of days a patient was hospitalized in a given year. I want a streamlined approach to this as I realize the dif function still requires a bit of coding and not exactly what I'd like. Any suggestions are appreciated, thx.
Days FromDate EndDate
1 22NOV2011 22NOV2011
21 23NOV2011 13DEC2011
64 14DEC2011 15FEB2012
664 01JAN2010 26OCT2011
2191 01JAN2010 31DEC2015
1793 01JAN2014 28NOV2014
833 25MAR2010 04JUL2012
So the issue is the EndDates that extend into the following year. For instance in line 3, how do I process how many days of the total 64 are in 2011 and how many are 2012 and still create a flag for each year? This is what I'd like to end up with:
Days FromDate EndDate CY2011 CY2012 Days2011 Days2012
1 22NOV2011 22NOV2011 1 0 1 0
21 23NOV2011 13DEC2011 1 0 21 0
64 14DEC2011 15FEB2012 1 1 18 46
data have;
input Days FromDate :date9. EndDate :date9.;
format FromDate EndDate date9.;
datalines;
1 22NOV2011 22NOV2011
21 23NOV2011 13DEC2011
64 14DEC2011 15FEB2012
664 01JAN2010 26OCT2011
2191 01JAN2010 31DEC2015
1793 01JAN2014 28NOV2014
833 25MAR2010 04JUL2012
;
proc sql;
select min(year(FromDate)), max(year(endDate)) into : min_ar TRIMMED, :max_ar TRIMMED
from have;
quit;
data want;
set have;
array t(&min_ar:&max_ar) days&min_ar-days&max_ar;
do _n_=&min_ar to &max_ar;
t(_n_)=0;
end;
_year=year(enddate)-year(FromDate);
if _year=0 then
do;
_n_=year(enddate);
_t=intck('days',FromDate,enddate)+1;
t(_n_)=_t;
end;
else if _year>0 then
do;
do _n_=year(fromdate) to year(enddate);
if _n_=year(fromdate) then _t=intck('days',FromDate,intnx('year', FromDate, 0, 'e'))+1;
else if _n_>year(fromdate) and _n_<year(enddate) then _t=intck('days',mdy(1,1,_n_),intnx('year',mdy(1,1,_n_), 0, 'e'))+1;
else if _n_=year(enddate) then _t=intck('days',mdy(1,1,_n_),enddate)+1;
t(_n_)=_t;
end;
end;
drop _:;
run;
The INTCK function will give you the number of days in a given year.
Creating flags can be done easily by the IFN function (or IF-THEN commands).
data have;
input Days FromDate :date9. EndDate :date9.;
format FromDate EndDate date9.;
datalines;
1 22NOV2011 22NOV2011
21 23NOV2011 13DEC2011
64 14DEC2011 15FEB2012
664 01JAN2010 26OCT2011
2191 01JAN2010 31DEC2015
1793 01JAN2014 28NOV2014
833 25MAR2010 04JUL2012
;
proc sql;
select min(year(FromDate)), max(year(endDate)) into : min_ar TRIMMED, :max_ar TRIMMED
from have;
quit;
data want;
set have;
array t(&min_ar:&max_ar) days&min_ar-days&max_ar;
do _n_=&min_ar to &max_ar;
t(_n_)=0;
end;
_year=year(enddate)-year(FromDate);
if _year=0 then
do;
_n_=year(enddate);
_t=intck('days',FromDate,enddate)+1;
t(_n_)=_t;
end;
else if _year>0 then
do;
do _n_=year(fromdate) to year(enddate);
if _n_=year(fromdate) then _t=intck('days',FromDate,intnx('year', FromDate, 0, 'e'))+1;
else if _n_>year(fromdate) and _n_<year(enddate) then _t=intck('days',mdy(1,1,_n_),intnx('year',mdy(1,1,_n_), 0, 'e'))+1;
else if _n_=year(enddate) then _t=intck('days',mdy(1,1,_n_),enddate)+1;
t(_n_)=_t;
end;
end;
drop _:;
run;
My concerns:
Sir/Madam,
1. I know you haven't provided the full dataset with patient ids, so I am guessing you would come up with that later to tweak
2. in your sample, 1793 01JAN2014 28NOV2014 this interval is only 332 days 🙂 and not 1793 days . I am not smart or creative, so i need precise immaculate samples to test
3. Kudos for offering a very interesting problem. I loved it
Oh yes thanks for the observation that was an error in number of days.
@jsandsk Ok great!, Do feel free to have any follow up questions if it relates to the same thread here otherwise of course you can always open up a new thread if you are looking for a new solution. Have fun! cheers
I noticed once I applied this to my full dataset that instances where there were observations >0, it shows as missing. Otherwise the formatting and zero counts are fine. What could be wrong with the calculations?
@jsandsk wrote:I noticed once I applied this to my full dataset that instances where there were observations >0, it shows as missing. Otherwise the formatting and zero counts are fine. What could be wrong with the calculations?
What do you mean by observations>0, can you show a sample of what you mean and what's happening?
data have;
input Days FromDate :date9. EndDate :date9.;
format FromDate EndDate date9.;
datalines;
1 22NOV2011 22NOV2011
21 23NOV2011 13DEC2011
64 14DEC2011 15FEB2012
664 01JAN2010 26OCT2011
2191 01JAN2010 31DEC2015
1793 01JAN2014 28NOV2014
833 25MAR2010 04JUL2012
;
data temp;
set have;
do date=FromDate to EndDate;
year=year(date);output;
end;
run;
proc summary data=temp nway;
class Days FromDate EndDate year;
output out=temp1;
run;
data temp2;
set temp1;
retain have 1;
run;
proc transpose data=temp2 out=x1 prefix=cy;
by Days FromDate EndDate;
id year;
var have;
run;
proc transpose data=temp2 out=x2 prefix=days;
by Days FromDate EndDate;
id year;
var _freq_;
run;
data x3;
merge x1 x2;
by Days FromDate EndDate;
drop _name_;
run;
proc stdize data=x3 out=want reponly missing=0;
run;
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.