Hi
I need to run a weekly report of the month every monday.
Let say now month is December.
The first report I need to run is on Monday (3rd-Dec-2018) which is fall in Week2 of Dec.
How I'm going to get the start and end date of the previous week?
Below is when the report should be run/generated.
Week1 report will be run in Monday Week2 : start=01-DEC-2018 , end=02-DEC-2018
Week2 report will be run in Monday Week3 : start=03-DEC-2018 , end=09-DEC-2018
Week3 report will be run in Monday Week4 : start=10-DEC-2018 , end=16-DEC-2018
Week4 report will be run in Monday Week5 : start=17-DEC-2018 , end=23-DEC-2018
Week5 report will be run in Monday Week6 : start=24-DEC-2018 , end=30-DEC-2018
Week6 report will be run in Monday Week1 of the following month. In this case Jan 2019 : start=31-DEC-2018 , end=31-DEC-2018
Thank you in advance.
Try this
data have(where=(weekday(start)=2 or day(start)=1));
format start end date9.;
do start='01Dec2018'd to '31Mar2019'd;
output;
end;
run;
proc sort data=have;
by descending start ;
run;
data have;
set have;
end=lag(start)-1;
if _n_=1 then end=intnx('month',start,0,'e');
run;
proc sort data=have out=want;
by start ;
run;
You will need to become familiar with the INTNX function. Here is one way. Within a DATA step:
start = intnx('week', today(), 0) +1;
end = start + 6;
format start end date9.;
This won't take care of the first week, because that one follows different rules (using a two-day time period). You will have to hard-code the dates for the first week.
Otherwise, you just have to run the program on a weekday to get the proper time period.
thank you. will try.
Try this
data have(where=(weekday(start)=2 or day(start)=1));
format start end date9.;
do start='01Dec2018'd to '31Mar2019'd;
output;
end;
run;
proc sort data=have;
by descending start ;
run;
data have;
set have;
end=lag(start)-1;
if _n_=1 then end=intnx('month',start,0,'e');
run;
proc sort data=have out=want;
by start ;
run;
Woww!!! you save my day ShiroAmada.
but can you explain a little bit what the below script doing:-
where=(weekday(start)=2 or day(start)=1)
end=lag(start)-1;
Thanks once more
I wanted to filter the records in the output dataset. In the data statement data have(where=(weekday(start)=2 or day(start)=1)); I used the sas functions WEEKDAY, LAG and DAY.
WEEKDAY function will extract the day of the week (in the date variable), result is 1 to 7 (1-Sunday and 7-Saturday).
DAY function is the actual day extracted from the date value (i.e 31Dec2018 will give you 31).
LAG function will copy the value of top record as it's value. This is why I sorted the dates in descending order.
As mentioned by @Astounding getting to know SAS functions (like INTNX) will help. The program I created made use of the sas functions WEEKDAY, DAY, LAG and INTNX. Check out these functions and see how to will help you in the future.
Glad I was able to help you out. Cheers!
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.