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;
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-...
@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;
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
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?
@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).
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-...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: