DATA Step, Macro, Functions and more

SAS weekly dates for rolling six month

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

SAS weekly dates for rolling six month

Hello ,

Hope you all doing good.

I need a help on weekly dates for my monthly reporting

I need to identify week start date and end date and thats for every Monday to Sunday pattern,

also its for rolling back 6 months to current reporting month

example:-

if report run is 1st November 2017, I need weekly start date and end date of every monday to sunday from 1st of April 2017 to 30th September 2017(6 months)

I have a historical SAS dataset with ID, Intake_date and Amount columns and i need to report Intake date wise weekly sales for rolling six month on a monthly basis.

Please suggest me with a sas code (it will be more helpful if any sas code automation suggestions).

Thanks,
Ravi


Accepted Solutions
Solution
‎10-20-2017 09:05 AM
Super Contributor
Posts: 320

Re: SAS weekly dates for rolling six month

Posted in reply to Ravikumar_RT

Hello,

 

data want;
format startDate endDate date date9. day $1.;

keep date day;

reportDate='1Nov2017'd;

endDate=intnx("day",reportDate,-1);
startDate=intnx("month",reportDate,-6);

date=startDate;

do while (date<=endDate and i<400);
    i+1;

	day=" ";
	if weekday(date)=1 then day="M";
	if weekday(date)=7 then day="S";

	date=intnx("day",date,1);

	if day ne " " then output;
end;
run;

View solution in original post


All Replies
Super User
Posts: 9,572

Re: SAS weekly dates for rolling six month

Posted in reply to Ravikumar_RT

To cumulate values per week, create a normalized date variable by using the intnx function:

weekdate = intnx('week',intake_date,0,'b');

You can then use proc summary with by weekdate to get weekly values.

A similar calculation can be done for months.

Or you can calculate the weekdate that was 6 months ago and start your report from that.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎10-20-2017 09:05 AM
Super Contributor
Posts: 320

Re: SAS weekly dates for rolling six month

Posted in reply to Ravikumar_RT

Hello,

 

data want;
format startDate endDate date date9. day $1.;

keep date day;

reportDate='1Nov2017'd;

endDate=intnx("day",reportDate,-1);
startDate=intnx("month",reportDate,-6);

date=startDate;

do while (date<=endDate and i<400);
    i+1;

	day=" ";
	if weekday(date)=1 then day="M";
	if weekday(date)=7 then day="S";

	date=intnx("day",date,1);

	if day ne " " then output;
end;
run;
Occasional Contributor
Posts: 7

Re: SAS weekly dates for rolling six month

Hi,
Thanks for your reply.

Above code is giving results from sunday to next monday i.e 01OCT2017(Sunday) to 02OCT2017(Monday) however i need sunday to previous monday exmple:- 25SEP2017 to 01OCT2017.

Please assist me.

Regards,
Ravi
Super Contributor
Posts: 320

Re: SAS weekly dates for rolling six month

Posted in reply to Ravikumar_RT
I'm not sure i understand what you're asking for. Can you provide an example for the desired output ?
Super User
Posts: 9,572

Re: SAS weekly dates for rolling six month

Posted in reply to Ravikumar_RT

... and some example data to work with. Use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert a dataset to a data step, if you don't want to (or can't yet) write the step yourself.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 7

Re: SAS weekly dates for rolling six month

Posted in reply to Ravikumar_RT
Hi,

I have a main input SAS dataset something like this

Master_ID INTAKE_DATE Division No_Of_Stores Sales_amt
1 01-Feb-2017 Central 10 50
1 02-Feb-2017 Central 20 90
2 03-Apr-2017 Southern 40 65
2 17-Apr-2017 Southern 15 70
3 10-Apr-2017 Southern 17 50
3 08-May-2017 Northern 19 90
5 01-Jul-2017 Northern 29 65
7 17-Sep-2017 Eastern 39 70
7 30-Sep-2017 Eastern 99 70

and I need all transactions which happend every starting of monday till end of sunday weekly basis in a month.
I want for rolling 6 months(from 1st april 2017 till 30th sep 2017). the output should be like this

Monday Sunday Division Total_Stores Total_Sales_amt
03-Apr-2017 09-Apr-2017 Central 2 500
10-Apr-2017 16-Apr-2017 Southern 9 900
17-Apr-2017 23-Apr-2017 Northern 10 65
24-Apr-2017 30-Apr-2017 Eastern 15 70


Thanks,
Ravi
Occasional Contributor
Posts: 7

Re: SAS weekly dates for rolling six month

Posted in reply to Ravikumar_RT

Hi All,

 

Can any one in this community assist me with my below mentioed request please.

 

Thanks,

Ravi

Super User
Posts: 9,572

Re: SAS weekly dates for rolling six month

Posted in reply to Ravikumar_RT

Please supply data in a data step, as already requested. It's not rocket science.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 7

Re: SAS weekly dates for rolling six month

Posted in reply to KurtBremser

Hi,

 

I have added just one small logic and it worked fine...Smiley Happy here is the code:

 

data want(rename = (date = Monday));
format startDate endDate date date9. day $1.;

keep date Sunday;

reportDate="&month_run."d;

endDate=intnx("day",reportDate,-1);
startDate=intnx("month",reportDate,-6);

date=startDate;

do while (date<=endDate and i<400);
    i+1;

    day=" ";
    if weekday(date)=1 then day="M";
    date=intnx("day",date,1);
    if day ne " " then output;
    Sunday = date+7;
end;
Format Sunday Date9.;
run;

 

 

Thanks all for your inputs and helping.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 177 views
  • 3 likes
  • 3 in conversation