## Calculating number of days for each month

Solved
Occasional Contributor
Posts: 16

# Calculating number of days for each month

Hi,

I am working to bucket the given number of days for each task to current and consecutive months from the given start date.

Source -

Because Feb2017 has 28 days in that month, number_of_days given is 48, so 48 - 28 = 20. Those 20 should fall into next month March2017

output needed -

Any suggestions are greatly appreciated !

Accepted Solutions
Solution
‎02-23-2017 02:11 PM
PROC Star
Posts: 311

## Re: Calculating number of days for each month

Below is what I'd do, but you'll probably get better solutions from others. I'm assuming that ending up with 45 days in April201 is an error, but the basic approach is to expand the observations to cover the necessary months and then transpose. You could probably do something with arrays, but I think this will be more dynamic. Depending on the real set of data, you might need to tweak it a bit, but it should get you started.

``````data have;
input task \$ start_date :date9. number_of_days;
format start_date date9.;
datalines;
;

data want;
set have;
/* Calculate the end date based on the number of days. */
end_date = start_date + number_of_days;
/* Iterate over how many months the span covers. */
do i = 1 to (intck('month', start_date, end_date) + 1);
/* For every month, determine the first and last days. */
fdom = intnx('month', start_date, i - 1, 'B');
ldom = intnx('month', start_date, i - 1, 'E');
/* Use these to calculate different date spans. */
full_month_days = ldom - fdom + 1;
part_month_days = min(full_month_days, end_date - fdom, ldom - start_date + 1);
/* Determine the number of days in the final segment. */
days = min(full_month_days, part_month_days);
/* Give the observation a name for transposition. */
name = substr(put(fdom, date9.), 3);
output;
end;
format end_date fdom ldom date9.;
run;

proc transpose data = want out = want2;
var days;
id name;
run;``````

All Replies
Solution
‎02-23-2017 02:11 PM
PROC Star
Posts: 311

## Re: Calculating number of days for each month

Below is what I'd do, but you'll probably get better solutions from others. I'm assuming that ending up with 45 days in April201 is an error, but the basic approach is to expand the observations to cover the necessary months and then transpose. You could probably do something with arrays, but I think this will be more dynamic. Depending on the real set of data, you might need to tweak it a bit, but it should get you started.

``````data have;
input task \$ start_date :date9. number_of_days;
format start_date date9.;
datalines;
;

data want;
set have;
/* Calculate the end date based on the number of days. */
end_date = start_date + number_of_days;
/* Iterate over how many months the span covers. */
do i = 1 to (intck('month', start_date, end_date) + 1);
/* For every month, determine the first and last days. */
fdom = intnx('month', start_date, i - 1, 'B');
ldom = intnx('month', start_date, i - 1, 'E');
/* Use these to calculate different date spans. */
full_month_days = ldom - fdom + 1;
part_month_days = min(full_month_days, end_date - fdom, ldom - start_date + 1);
/* Determine the number of days in the final segment. */
days = min(full_month_days, part_month_days);
/* Give the observation a name for transposition. */
name = substr(put(fdom, date9.), 3);
output;
end;
format end_date fdom ldom date9.;
run;

proc transpose data = want out = want2;
var days;
id name;
run;``````
Posts: 1,312

## Re: Calculating number of days for each month

[ Edited ]

Here's a minor modificatino of the above, shortening the program a litte:

``````data have;
input task :\$5. start_date :date9.  number_of_days;
format start_date date9.;
datalines;
run;

data need (keep=task start_date number_of_days monthname monthdays);
set have;
do d=start_date by 0 until(d>=start_date+number_of_days);
monthname=left(put(d,yymon7.));
limit =min(start_date+number_of_days,intnx('month',d,0,'end')+1);
monthdays=limit-d;
d=intnx('month',d,1,'beg');
output;
end;
run;
proc transpose data=need out=want ;
var monthdays;
id monthname;
run;``````
PROC Star
Posts: 311

## Re: Calculating number of days for each month

The only concern I'd have with alternate solution is excluding the year in the transposed variable name. It might not matter, but if the number of days is ever more than a year or cross into a new year, you might end up with duplicates that won't transpose or ambiguous column names.

Posts: 1,312

## Re: Calculating number of days for each month

collinelliot wrote:

The only concern I'd have with alternate solution is excluding the year in the transposed variable name. It might not matter, but if the number of days is ever more than a year or cross into a new year, you might end up with duplicates that won't transpose or ambiguous column names.

@collinelliot

Good point.  To address that issue, I editted the date format inserted into monthname from MONNAME8.  to YYMON7.

THX

Super User
Posts: 10,700

## Re: Calculating number of days for each month

``````data have;
input Task  \$     start_date   : date9.    number_of_days;
format start_date date7.;
cards;
;
run;
data temp;
set have;
do i=0 to number_of_days-1;
date=start_date+i;output;
end;
run;
proc freq data=temp noprint;
table task*start_date*date/out=temp1 list nopercent nocum ;
format date monyy5.;
run;
proc transpose data=temp1 out=temp2(drop=_:);
var count;
id date;
run;
proc stdize data=temp2 out=want missing=0 reponly;
run;``````
Occasional Contributor
Posts: 16

## Re: Calculating number of days for each month

Thank you everyone for your prompt solution.

☑ This topic is solved.

Discussion stats
• 6 replies
• 1310 views
• 4 likes
• 4 in conversation