BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jsandsk
Obsidian | Level 7

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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
novinosrin
Tourmaline | Level 20

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;

novinosrin
Tourmaline | Level 20

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

jsandsk
Obsidian | Level 7

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

novinosrin
Tourmaline | Level 20

@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

jsandsk
Obsidian | Level 7

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?

novinosrin
Tourmaline | Level 20

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

Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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