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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.