Macro to resolve dates like fist day of the month, last date of the month to Date9. etc

[Stalk]

Any one have any ready made macro to get different days of the month into Date9. format.

first day of the month,

first day of the last week

Last day of the last week

last saturday of last week

First day of the current week etc.. some thing similar

data _null_;
yday=put(date()-1,date9.);
today=put(date(),date9.);
lsat=put(date()-1,date9.);*take last Saturday;
to = put(date(),worddate18.);
mthy= put(date(),monyy7.);
%let fdlw=%sysfunc(intnx(week,%sysfunc(today()),-1,b),date9.);
%put first_day=&fdlw ;
%let last_sat=%sysfunc(intnx(week.7,%sysfunc(today()),0,b),date9.);
%put last_sat=&last_sat ;
%let ldlw=%sysfunc(intnx(week.7,%sysfunc(today()),0,b),date9.);*Last Saturday;
%put ldlw=&ldlw ;
%let fdcm_=put(date()-15,date9.);

call symput('rptdt',to);
call symput('rptnumdt',today); *05FEB2021;
call symput('ydt',yday);*05FEB2021;
call symput('fdcm',fdcm_ );
call symput('my',mthy);*FEB2021;
run;

%let ydate= &ydt.; * yesterday's date ;
%let Mydate= &my.; * yesterday's date ;
%let From_date= &ydt. ;
%let To_date=&rptnumdt.;
%let FirstDay_CurMon=&fdcm.;
%put _all_;

[qoit]

I do have a date macro but it creates a lot of variables, so I have written a quick DATA _NULL_, see below if that fits your need? Note that it is assumed that you are happy with SAS's logic of the week start to end i.e. Sunday to Saturday.

data _null_;
	TODAY = today();

	*first day of current month (cm);
	call symputx('FIRST_DAY_CM',put(intnx('month',TODAY,0,'b'),date9.),'g');

	*last day of current month (cm);
	call symputx('LAST_DAY_CM',put(intnx('month',TODAY,0,'e'),date9.),'g');

	*first day of previous month (pm);
	call symputx('FIRST_DAY_PM',put(intnx('month',TODAY,-1,'b'),date9.),'g');

	*last day of previous month (pm);
	call symputx('LAST_DAY_PM',put(intnx('month',TODAY,-1,'e'),date9.),'g');

	*first day of current week (cw);
	call symputx('FIRST_DAY_CW',put(intnx('week',TODAY,0,'b'),date9.),'g');

	*last day of current week (cw);
	call symputx('LAST_DAY_CW',put(intnx('week',TODAY,0,'e'),date9.),'g');

	*first day of previous week (pw);
	call symputx('FIRST_DAY_PW',put(intnx('week',TODAY,-1,'b'),date9.),'g');

	*last day of previous week (pw);
	call symputx('LAST_DAY_PW',put(intnx('week',TODAY,-1,'e'),date9.),'g');

	*Last Saturday of previous week (pw);
	call symputx('LAST_SAT_PW',put(intnx('week.7',TODAY,0,'b'),date9.),'g');
run;

%put _global_;

[SASKiwi]

What's your definition of "last week"? Do your weeks start on Sunday and end on Saturday, or start on Monday and end on Sunday or some other definition? If today is Monday, is yesterday, that is Sunday, the last day of last week?

[Tom]

Macro code executes before the SAS code that it generates runs.  So do not embed %LET and %PUT statements into the middle of a data step.  It will just confuse you about when the statements will execute and what your code is doing.

Also what do you think the macro variable FDCM_ contains?  How does it print if you run %PUT &FDCM?

What do you think the data step variable FDCM_ contains?  Where have you given it a value?

%let fdlw=%sysfunc(intnx(week,%sysfunc(today()),-1,b),date9.);
%put first_day=&fdlw ;
%let last_sat=%sysfunc(intnx(week.7,%sysfunc(today()),0,b),date9.);
%put last_sat=&last_sat ;
%let ldlw=%sysfunc(intnx(week.7,%sysfunc(today()),0,b),date9.);
%put ldlw=&ldlw ;
%let fdcm_=put(date()-15,date9.);

data _null_;
  yday=put(date()-1,date9.);
  today=put(date(),date9.);
  lsat=put(date()-1,date9.); 
*take last Saturday;
  to = put(date(),worddate18.);
  mthy= put(date(),monyy7.);
*Last Saturday;
  call symput('rptdt',to);
  call symput('rptnumdt',today); 
*05FEB2021;
  call symput('ydt',yday);
*05FEB2021;
  call symput('fdcm',fdcm_ );
  call symput('my',mthy);
*FEB2021;
run;

%let ydate= &ydt.; 
* yesterday's date ;
%let Mydate= &my.; 
* yesterday's date ;
%let From_date= &ydt. ;
%let To_date=&rptnumdt.;
%let FirstDay_CurMon=&fdcm.;
%put _all_;

[Stalk]

Thank you qoit. My requirement for week calculation is from Sunday to Saturday
Tom, I know my code is a bit messy as I had to change my code several times because my user change their mind every hour. Some times they need the report for rolling 15 days. Some times weekly counts, sometimes daily counts , some times show aggregate prior week counts and display daily counts for this week etc..
SAS Kiwi, Yes my week starts from Sunday to Saturday.
Some times I have to groups the dates to get the weekly counts..
if '24JAN2021'd <= Date_rec <= '30JAN2021'd then Category='24JAN2021-30JAN2021';
else if '31JAN2021'd <= Date_rec <= '06FEB2021'd then Category='31JAN2021-06FEB2021';
else if '07FEB2021'd <= Date_rec <= '13FEB2021'd then Category='07FEB2021-13FEB2021';
....
else Category='No Date';

[Tom · Accepted solution]

In general it is easier to work with these types of calculated dates in macro code if you just leave them as the raw number of days.  The SAS code does not care if you make them look like the date strings that humans understand.

But in your case you appear to want to use them in a descriptive text string, so for those places you would want to use a string that humans will understand (it could even be a string that would be hard for SAS code to understand).

The first thing to watch out is that when referencing macro variables in string or date literals is you need to use double quotes on the outside.  SAS itself doesn't care which character you use to quote, but the macro processor ignores strings that are enclosed in the single quote characters in your example code.

If the user is going to type the dates then use a format that humans understand.  If you use a string that the DATE informat understands then you can use the string in a date literal.  For example you might want to ask them to specify a range.

%let start=01JAN2021 ;
%let stop=30JAN2021 ;

You can then use those two strings in your code like this:

data want ;
  set have;
  where date between "&start"d and "&stop"d ;
  category="&start - &stop" ;
run;

If you want to generate a weekly category variable then perhaps just normal SAS code is all you need, no macro code at all. 

data want;
  set have ;
  length category $20 ;
  category=catx('-',put(intnx('week',date_rec,0,'b'),date9.),put(intnx('week',date_rec,0,'e'),date9.));
run;

[Stalk]

category=catx('-,put(intnx('week',date_rec,0,'b'),date9.),put(intnx('week',date_rec,0,'e'),date9.));
works perfectly. Thank you so much for simple solution.
I have a follow up question... When I try to use this Category field in Proc report as across field with order=internal. the dates are not showing up in order ..
03JAN2021-09JAN2021 06DEC2020-12DEC2020 08NOV2020-14NOV2020
Any suggestions?

[Tom]

If you want strings to sort chronologically then use year-month-day ordering when generating the strings.

Try the YYMMDD format instead of the DATE format.

[Stalk]

Works but dates looks very messy and hard to understand for the end user.. Is there any other option?
21-01-03-21-01-09 21-01-10-21-01-16 21-01-17-21-01-23 21-01-24-21-01-30
"21-01-03 To 21-01-09" "21-01-10 To 21-01-16" "21-01-17 To 21-01-23" "21-01-24 To 21-01-30"

[Tom]

Do not use two digit years, remember Y2K.

"2021-01-03 To 2021-01-09" 

[Stalk]

I am getting errors when I use YYYYMMDD or YYMMDD10. in this..
category=catx(' To ',put(intnx('week',datepart(Date_rec),0,'b'),YYMMDD.),put(intnx('week',datepart(date_rec),0,'e'),YYMMDD.));

[Tom]

length category $24 ;
category=catx(' To '
  ,put(intnx('week',datepart(Date_rec),0,'b'),YYMMDD10.)
  ,put(intnx('week',datepart(date_rec),0,'e'),YYMMDD10.)
);

[andreas_lds]

Could you post the data you have (as dataset using datalines) and show what you expect as result. I think that manually creating the category variable is causing the trouble. Maybe a self-defined format could be used.