topic Re: How to calculate days for each month using two dates in SAS Programming

How to calculate days for each month using two dates

Scooby3g — Tue, 31 Oct 2023 20:41:31 GMT

Hi All,

I've coded this in the past but for the life of me I cannot recall how I did it in the past. It feels like its on the tip of my mind but I just cant remember.

How can I calculate number of days for each month using date1 and date2.

For example, obs 1 will have 31 days for 2023_01 and 4 days for 2023_02

data have;
infile datalines ;
format date1 date2 date7.;
input date1 :date7.  State:$  date2 :date7. ID ;
datalines;
01Jan23	    NY	04Feb23	 1
01Jan23	    NY	04Feb23	 1
01Jan23		NY	04Feb23	 1
01Jan23		NY	04Feb23	 1
01Jul23 	CA	04Jul23	 2
01Jul23 	CA	04Jul23	 2
01Jul23 	CA	04Jul23	 2
;
run;

Re: How to calculate days for each month using two dates

Reeza — Tue, 31 Oct 2023 20:47:42 GMT

Subtract the two dates and add 1?

Re: How to calculate days for each month using two dates

Scooby3g — Tue, 31 Oct 2023 20:49:39 GMT

that only gives number of days between date1 and date2. I want to get days by month by state.

Re: How to calculate days for each month using two dates

Reeza — Tue, 31 Oct 2023 21:02:06 GMT

Please show expected output. Is it always two months or will the amount of months vary.

Re: How to calculate days for each month using two dates

Scooby3g — Tue, 31 Oct 2023 21:12:03 GMT

state yr_month days
NY 2023_01 31
NY 2023_02 4
CA 2023_07 4

The number of months would depend on how many months are between date1 and date1

Re: How to calculate days for each month using two dates

ballardw — Tue, 31 Oct 2023 21:51:48 GMT

@Scooby3g wrote:
state yr_month days
NY 2023_01 31
NY 2023_02 4
CA 2023_07 4

The number of months would depend on how many months are between date1 and date1

Doesn't bear much resemblance to example input data. You example in the first post repeats dates:

So there request is for each "start date" "end_date" to increment the month as needed, create an additional output observation with the "new"month start and the number of days?

Something like this perhaps:

data have;
infile datalines ;
format date1 date2 date7.;
input date1 :date7.  State:$  date2 :date7. ID ;
datalines;
01Jan23	    NY	04Feb23	 1
01Jul23 	CA	04Jul23	 2
15Jan23 aa  21May23 3
;
run;

data want;
   set have;
   do i=0 to intck('month',date1,date2);
      days=1+ intck('day',date1,min(intnx('month',date1,0,'e'),date2));
      output;
      date1=intnx('month',date1,1,'b');
   end;
   drop  i;
run;

Re: How to calculate days for each month using two dates

Reeza — Tue, 31 Oct 2023 21:53:39 GMT

This does not account for dates that may start not on the first of the month for date1.

data want;
set have;
by id;
if first.id;
nmonths = intck('month', date1, date2);

do i = 0 to nmonths;
    month = intnx('month', date1, i, 'b');
    format month yymmd7.;
    num_days = intck('days', intnx('month', date1, i, 'b'), min(date2, intnx('month', date1, i, 'e')))+1;
    output;
end;

keep id state month num_days;
run;

@Scooby3g wrote:
state yr_month days
NY 2023_01 31
NY 2023_02 4
CA 2023_07 4

The number of months would depend on how many months are between date1 and date1

Re: How to calculate days for each month using two dates

Scooby3g — Thu, 02 Nov 2023 14:10:45 GMT

Hi @Reeza and @ballardw ,

Thank you both for your feedback. What I am trying to do is calculate patient days during the patients admission for each month on each unit they stay on. date1 would be the patients admission date and date2 would be the discharge date. Consider State as the location of the patient. I tried the codes you suggested but added an additional row to the "HAVE" table data step before running your suggestions.

data have;
infile datalines ;
format date1 date2 date7.;
input date1 :date7.  State:$  date2 :date7. ID ;
datalines;
01Jan23	    NY	04Feb23	 1
01Jan23	    NY	04Feb23	 1
01Jan23		NY	04Feb23	 1
01Jan23		NY	04Feb23	 1
02Jul23 	CA	04Jul23	 2
02Jul23 	CA	04Jul23	 2
02Jul23 	CA	04Jul23	 2
20Sep23		CA  30Sep23  3
;
run;

When I try @Reeza's suggestion, obs 8 should be 11 (sep 20 to sep 30) but the output is 30 instead.

When I tried @ballardw 's suggestion was missing the Year_month that i want the number of days to be attributed to. Is there a way to dedup to have just the below obs/row (obs 1, 2, 9, 12) along with the year_month the days are being attributed to?

Re: How to calculate days for each month using two dates

ballardw — Thu, 02 Nov 2023 14:27:20 GMT

@Scooby3g wrote:
When I try @Reeza's suggestion, obs 8 should be 11 (sep 20 to sep 30) but the output is 30 instead.

When I tried @ballardw 's suggestion was missing the Year_month that i want the number of days to be attributed to.
Please describe exactly how "year_month" is missing?

If you apply a format of YYMMD7 to date1 I think you will see the VALUE in the APPEARANCE you want.

It is a bad idea to store anything that should actually be a date as other than a date value.

"dedupe" Proc Sort with the nodupkey . Or don't put repeated values into the process to begin with (sort BEFORE this step).

Re: How to calculate days for each month using two dates

Scooby3g — Thu, 02 Nov 2023 15:35:53 GMT

I think I may have found what I needed in this post https://communities.sas.com/t5/New-SAS-User/Calculating-patietn-days-for-each-month-using-start-and-end/td-p/583954