topic Re: Days in a given month represented by a date range in SAS Programming

Days in a given month represented by a date range

jkem — Fri, 25 Mar 2022 19:16:30 GMT

Hello, I am trying to create a series of count variables for two years (24 variables January19-December19 and January20-December20) that reflect the number of days in each month from a given date range. For example, consider:

data WORK.DRUG(label='Drug Episode');
   infile datalines;
   input enrolid:$8. startdate:mmddyy10. enddate:mmddyy10.;
 datalines;
 12344 02/02/2019 03/04/2019
 32456 03/18/2019 04/17/2019
 21424 04/26/2019 05/26/2019
 37625 05/25/2019 06/25/2019
 34635 12/28/2019 01/28/2020
 ;;;;

For enrolid=12344, the result I want is: January19=0, February19=26, March19=4, April19=0...etc. I have two years of data, so dates may occasionally cross years (eg last line of sample code)

I have tried to follow the advice from a previous post (https://communities.sas.com/t5/SAS-Programming/Get-Number-of-days-in-each-month-between-two-dates/td-p/548014) by converting my dates to SAS date constants and running a couple of the suggested options, but get the error message my variables are uninitialized, so I think perhaps this code is for specific one-time inputs rather than a list of variables.

Appreciate any insight. I'm using 9.4

Re: Days in a given month represented by a date range

Astounding — Fri, 25 Mar 2022 22:23:24 GMT

The methods in your link look like they should work. I'm not sure how you translated the program for your application, but here is a way that should be OK:

data days;
   set drug;
   do date = startdate to enddate;
      output;
   end;
run;

proc freq data=days;
   tables id * date / output out=counts;
   format date monyy7.;
run;

Re: Days in a given month represented by a date range

ballardw — Fri, 25 Mar 2022 23:34:14 GMT

You did not very clearly specify what the output should look like. Here is my stab.

data WORK.DRUG(label='Drug Episode');
   infile datalines;
   input enrolid:$8. startdate:mmddyy10. enddate:mmddyy10.;
   format  startdate mmddyy10. enddate mmddyy10.;
 datalines;
 12344 02/02/2019 03/04/2019
 32456 03/18/2019 04/17/2019
 21424 04/26/2019 05/26/2019
 37625 05/25/2019 06/25/2019
 34635 12/28/2019 01/28/2020
 11111 12/28/2019 04/13/2020
 ;

 data need;
    set work.drug;
    m='01Jan2019'd;
    do until (m= '01Dec2020'd);
       mtext= put(m,monyy7.);
       /* month well before start date*/
       If intck('month',m,intnx('month',startdate,0,'B')) > 0 then days=0;
       /* month the same as the start date*/
       else if m=intnx('month',startdate,0,'B')  then 
         days=intck('day',startdate,intnx('month',startdate,0,'E'));
       /* end of period in the month*/
       else if m=intnx('month',enddate,0,'B') then days= day(enddate);
       /* month after the end of the period*/
       else if intck('month',m,intnx('month',enddate,0,'E')) < 0 then days=0;
       /* month completely within interval*/
       else days= day(intnx('month',m,0,'E'));
       output;
       call missing(days);
       m= intnx('month',m,1,'b');
    end;
    format m monyy.;
run;

proc transpose data=need out=want(drop=_name_)
   ;
   by notsorted enrolid startdate enddate  ;
   var days;
   id mtext;
run;

Your data did not include anything with a start end period longer than a month, or describe the result intended. I added one at the end of the data set so you can see if that is proper result.

Note the comments for the different comparisons as to which each interval is setting the value for the Days variable.

There may be something slicker, especially if your intervals ALWAYS are basically one month and you state that to be the case.

Note that I used 4-digit years. There is NO good that comes from using two digit years. Also made all of the variable names the same length.

It may be that "wide" data really isn't needed. How do you expect to use the resulting set? Normally placing actual data, the month/year, into a Variable is poor practice. Also sorts of the variable names result in awkward behavior.