topic Re: SAS weekly dates for rolling six month in SAS Programming

SAS weekly dates for rolling six month

Ravikumar_RT — Thu, 19 Oct 2017 12:30:01 GMT

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

Re: SAS weekly dates for rolling six month

Kurt_Bremser — Thu, 19 Oct 2017 12:48:06 GMT

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.

Re: SAS weekly dates for rolling six month

gamotte — Thu, 19 Oct 2017 12:52:51 GMT

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;

Re: SAS weekly dates for rolling six month

Ravikumar_RT — Thu, 19 Oct 2017 13:16:27 GMT

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

Re: SAS weekly dates for rolling six month

gamotte — Thu, 19 Oct 2017 13:23:27 GMT

I'm not sure i understand what you're asking for. Can you provide an example for the desired output ?

Re: SAS weekly dates for rolling six month

Kurt_Bremser — Thu, 19 Oct 2017 13:25:29 GMT

... 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-data-AKA-generate/ta-p/258712 to convert a dataset to a data step, if you don't want to (or can't yet) write the step yourself.

Re: SAS weekly dates for rolling six month

Ravikumar_RT — Thu, 19 Oct 2017 14:27:09 GMT

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

Re: SAS weekly dates for rolling six month

Ravikumar_RT — Fri, 20 Oct 2017 09:09:39 GMT

Hi All,

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

Thanks,

Ravi

Re: SAS weekly dates for rolling six month

Kurt_Bremser — Fri, 20 Oct 2017 09:16:17 GMT

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

Re: SAS weekly dates for rolling six month

Ravikumar_RT — Fri, 20 Oct 2017 13:08:26 GMT

Hi,

I have added just one small logic and it worked fine...:) 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.