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

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;
1 ACCEPTED SOLUTION
9 REPLIES 9
Reeza
Super User
Subtract the two dates and add 1?
Scooby3g
Obsidian | Level 7
that only gives number of days between date1 and date2. I want to get days by month by state.
Reeza
Super User
Please show expected output. Is it always two months or will the amount of months vary.
Scooby3g
Obsidian | Level 7
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
ballardw
Super User

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

 

 

 

Reeza
Super User

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

 

Scooby3g
Obsidian | Level 7

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.

Scooby3g_0-1698930462539.png

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?

Scooby3g_1-1698931303320.png

 

ballardw
Super User


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

 

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 9 replies
  • 1141 views
  • 7 likes
  • 3 in conversation