topic Re: How to get start and end date of the previous week of the month in SAS Programming

How to get start and end date of the previous week of the month

imza72 — Thu, 13 Dec 2018 01:36:03 GMT

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.

Re: How to get start and end date of the previous week of the month

Astounding — Thu, 13 Dec 2018 03:02:58 GMT

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.

Re: How to get start and end date of the previous week of the month

imza72 — Thu, 13 Dec 2018 03:30:01 GMT

thank you. will try.

Re: How to get start and end date of the previous week of the month

ShiroAmada — Thu, 13 Dec 2018 04:09:37 GMT

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;

Re: How to get start and end date of the previous week of the month

imza72 — Thu, 13 Dec 2018 04:34:32 GMT

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

Re: How to get start and end date of the previous week of the month

ShiroAmada — Thu, 13 Dec 2018 04:55:50 GMT

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!

Re: How to get start and end date of the previous week of the month

imza72 — Thu, 13 Dec 2018 04:59:23 GMT

thanks for your promtpresponse