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
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;
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.
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;
... 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.
Hi All,
Can any one in this community assist me with my below mentioed request please.
Thanks,
Ravi
Please supply data in a data step, as already requested. It's not rocket science.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.