DATA Step, Macro, Functions and more

Difference in Days for Consecutive Years

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Difference in Days for Consecutive Years

[ Edited ]

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

 

 


Accepted Solutions
Solution
‎01-04-2018 10:26 PM
Super User
Posts: 2,061

Re: Difference in Days for Consecutive Years

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,275

Re: Difference in Days for Consecutive Years

[ Edited ]

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

--
Paige Miller
Solution
‎01-04-2018 10:26 PM
Super User
Posts: 2,061

Re: Difference in Days for Consecutive Years

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;

Super User
Posts: 2,061

Re: Difference in Days for Consecutive Years

[ Edited ]

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 Smiley Happy 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

Occasional Contributor
Posts: 18

Re: Difference in Days for Consecutive Years

Posted in reply to novinosrin

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

Super User
Posts: 2,061

Re: Difference in Days for Consecutive Years

@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

Occasional Contributor
Posts: 18

Re: Difference in Days for Consecutive Years

Posted in reply to novinosrin

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?

Super User
Posts: 2,061

Re: Difference in Days for Consecutive Years


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?

Super User
Posts: 10,850

Re: Difference in Days for Consecutive Years

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 364 views
  • 1 like
  • 4 in conversation