BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kgeorge130
Fluorite | Level 6

I have a report that I am working on that I need to the number of days in each month that a event occurred on between two dates.

 

for example 

 

Start Date  :15MAY2018

STOP Date:22JUL2018

 

May Days: 

Jun Days:

Jul Days:

 

Dates may also span across years as well ,

 

Any thoughts?

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data x;
    startdate='15MAY2018'd;
    enddate='22JUL2018'd;


	n+1;
    do date=startdate to enddate;
	 output;
	end;
run;
proc freq data=x noprint;
table n*date/out=want list;
format date monyy7.;
run;

View solution in original post

7 REPLIES 7
PaigeMiller
Diamond | Level 26
data _null_;
    startdate='15MAY2018'd;
    enddate='22JUL2018'd;
    date = intnx('month',startdate,0,'b');
    do while (date<enddate);
         numdays=1+intck('days',max(date,startdate+1),
             min(enddate,intnx('month',date,0,'e')));
         put date date7. +1 numdays=;
         date=intnx('month',date,1,'b');
     end;
run;
--
Paige Miller
ballardw
Super User

@kgeorge130 wrote:

I have a report that I am working on that I need to the number of days in each month that a event occurred on between two dates.

 

for example 

 

Start Date  :15MAY2018

STOP Date:22JUL2018

 

May Days: 

Jun Days:

Jul Days:

 

Dates may also span across years as well ,

 

Any thoughts?

 


My first thought is given two dates how do we know which days an "event occurred on"?

As @PaigeMiller showed it isn't difficult to get the number of days by month. But an "event"?

 

One suspects we would need an example of the data you are processing and a definition of what constitutes an event for a better answer. And hopefully the events have an actual SAS date value associated.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Ksharp
Super User
data x;
    startdate='15MAY2018'd;
    enddate='22JUL2018'd;


	n+1;
    do date=startdate to enddate;
	 output;
	end;
run;
proc freq data=x noprint;
table n*date/out=want list;
format date monyy7.;
run;
PaigeMiller
Diamond | Level 26

@Ksharp 

that's a lot easier than what I suggested!

--
Paige Miller
kgeorge130
Fluorite | Level 6

Thanks looks like this might work, 

 

one quick question,  this is probably easy but I am fairly new to SAS programming so still learning. 

 

If I wanted to bring in data from an existing  table how would I do that?

 

I tried changing the data x;  to point to my data set but that did not work. 

 

thanks!

Ksharp
Super User
data x;
    set have ;  /*<- my dataset*/


	n+1;
    do date=startdate to enddate;
	 output;
	end;
run;
Tom
Super User Tom
Super User

Use INTNX() to adjust dates. Use INTCK() to know how many months to output.

data have;
  startdate='15MAY2018'd;
  enddate='22JUL2018'd;
  format _all_ date9.;
run;
data want;
  set have;
  do offset=0 to intck('month',startdate,enddate);
    month = intnx('month',startdate,offset,'b');
    days = min(enddate,intnx('month',month,0,'e'))-max(startdate,month)+1;
    output;
  end;
  format month monyy.;
run;
proc print;
run;
Obs    startdate      enddate    offset    month    days

 1     15MAY2018    22JUL2018       0      MAY18     17
 2     15MAY2018    22JUL2018       1      JUN18     30
 3     15MAY2018    22JUL2018       2      JUL18     22

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
  • 7 replies
  • 1524 views
  • 2 likes
  • 5 in conversation