topic Re: Difference in Days for Consecutive Years in SAS Programming

Difference in Days for Consecutive Years

jsandsk — Thu, 04 Jan 2018 18:03:11 GMT

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

Re: Difference in Days for Consecutive Years

PaigeMiller — Thu, 04 Jan 2018 18:22:12 GMT

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).

Re: Difference in Days for Consecutive Years

novinosrin — Thu, 04 Jan 2018 20:06:46 GMT

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;

drop _:;

run;

Re: Difference in Days for Consecutive Years

novinosrin — Thu, 04 Jan 2018 21:05:17 GMT

My concerns:

@jsandsk

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

Re: Difference in Days for Consecutive Years

jsandsk — Fri, 05 Jan 2018 03:06:29 GMT

Oh yes thanks for the observation that was an error in number of days.

Re: Difference in Days for Consecutive Years

novinosrin — Fri, 05 Jan 2018 03:38:38 GMT

@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

Re: Difference in Days for Consecutive Years

Ksharp — Fri, 05 Jan 2018 14:22:28 GMT

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;

Re: Difference in Days for Consecutive Years

jsandsk — Tue, 09 Jan 2018 02:16:17 GMT

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?

Re: Difference in Days for Consecutive Years

novinosrin — Tue, 09 Jan 2018 04:13:34 GMT

@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?