topic Re: Date in macro variables in SAS Programming

Date in macro variables

Pandu2 — Fri, 11 Mar 2022 06:57:57 GMT

Hi All,
I require a small assistance from you guys regarding.
I would like to have my table name embedded with week of today and year of today.
I.e myfile102022
I tried everything like
Data myfile&d._&d1.;
%let d=week(today());
%let d1=year(today());
%put &d &d1;
run;
But the it isn't printing the values of d and d1 instead it is taking the values as is i.e
myfileweek(today())_year(today())
Any help would be greatly appreciated. Thanks in advance.

Re: Date in macro variables

Kurt_Bremser — Fri, 11 Mar 2022 06:58:15 GMT

Macro statements like %LET and macro variables are resolved while the code is fetched for compiling/execution, so your code is equivalent to

%let d=week(today());
%let d1=year(today());
%put &d &d1;

Data myfile&d._&d1.;
run;

and that is the correct sequence for writing macro code.

You store the text "week(today())" in your macro variable d, and characters like parentheses are clearly not allowed in dataset names. The content of the macro variables is visible in your %PUT.

To evaluate data step functions while the %LET is executed, you need to wrap them in %SYSFUNC:

%let d=%sysfunc(week(%sysfunc(today()),z2.);
%let d1=%sysfunc(year(%sysfunc(today()));

I added the Z2. format to the first%SYSFUNC so that week 9 (09) comes before week 10.

And for better handling, use the correct hierarchical order in your dataset name:

data myfile&d1._&d.;

Re: Date in macro variables

Pandu2 — Fri, 11 Mar 2022 07:12:32 GMT

Does macro variables work under proc SQL?. And also got the error like.
Libref myfile is not assigned even though it isn't a library. Any advice please

Re: Date in macro variables

Kurt_Bremser — Fri, 11 Mar 2022 07:37:17 GMT

I see I missed parentheses. See this updated example:

%let d=%sysfunc(week(%sysfunc(today())),z2.);
%let d1=%sysfunc(year(%sysfunc(today())));

data myfile&d1._&d.;
set sashelp.class;
run;

which gets this log:

 69         %let d=%sysfunc(week(%sysfunc(today())),z2.);
 70         %let d1=%sysfunc(year(%sysfunc(today())));
 71         
 72         data myfile&d1._&d.;
 73         set sashelp.class;
 74         run;
 
 NOTE: There were 19 observations read from the data set SASHELP.CLASS.
 NOTE: The data set WORK.MYFILE2022_10 has 19 observations and 5 variables.

And yes, you can use macro variables in all SAS code, because the macro preprocessor works with the code text before it is sent to execute. So the macro variables will be resolved before the SQL code is sent to the SQL interpreter.

Re: Date in macro variables

Pandu2 — Fri, 11 Mar 2022 07:32:33 GMT

Thanks a bunch it is working now and I kept work library before the filename so that it didn't give the error which I previously got. Can't be thankful enough.

Re: Date in macro variables

Patrick — Fri, 11 Mar 2022 09:52:15 GMT

@Pandu2 wrote:
Thanks a bunch it is working now and I kept work library before the filename so that it didn't give the error which I previously got. Can't be thankful enough.

@Pandu2 Then mark the answer that was most helpful to you as solution.

Re: Date in macro variables

Pandu2 — Mon, 14 Mar 2022 06:45:43 GMT

Hii,
In your code
%let d=%sysfunc(week(%sysfunc(today())),z2.).
I tried to get the prior week of it but couldn't figure out the best. Could you please help me on that.
Thanks

Re: Date in macro variables

Patrick — Mon, 14 Mar 2022 06:54:33 GMT

@Pandu2 wrote:
Hii,
In your code
%let d=%sysfunc(week(%sysfunc(today())),z2.).
I tried to get the prior week of it but couldn't figure out the best. Could you please help me on that.
Thanks

%let d=%sysfunc(week(%sysfunc(today())),z2.);
%put &=d;

%let d2=%sysfunc(week(%eval(%sysfunc(today())-7)),z2.);
%put &=d2;

Re: Date in macro variables

Kurt_Bremser — Mon, 14 Mar 2022 06:58:47 GMT

Subtract 7 from the date. To make (integer) calculations in macro language, use the %EVAL function:

%let d=%sysfunc(week(%eval(%sysfunc(today())-7)),z2.);

Re: Date in macro variables

Pandu2 — Mon, 14 Mar 2022 07:04:28 GMT

Thankyou for your assistance. The week represents Fiscal week but not sure subtracting -7 give the same until it hits the next week. Please do check by giving tomorrow's date instead of today's.
Thanks.

Re: Date in macro variables

Patrick — Mon, 14 Mar 2022 07:07:21 GMT

@Pandu2 wrote:
Thankyou for your assistance. The week represents Fiscal week but not sure subtracting -7 give the same until it hits the next week. Please do check by giving tomorrow's date instead of today's.
Thanks.

@Pandu2

The source value created by the today() function is a count of days since 1/1/1960.

If the week() function returns the correct value then substracting 7 days from the source value before applying the week() function will work.

Re: Date in macro variables

Pandu2 — Mon, 14 Mar 2022 07:42:33 GMT

Thankyou

Re: Date in macro variables

Pandu2 — Mon, 14 Mar 2022 07:43:11 GMT

Thanks a ton

Re: Date in macro variables

Kurt_Bremser — Mon, 14 Mar 2022 09:48:07 GMT

Which values do you expect then for the dates from 2022-03-14 to 2022-03-21?

Re: Date in macro variables

Tom — Mon, 14 Mar 2022 13:56:23 GMT

@Kurt_Bremser wrote:

Subtract 7 from the date. To make (integer) calculations in macro language, use the %EVAL function:
%let d=%sysfunc(week(%eval(%sysfunc(today())-7)),z2.);

Note that if the expression is being passed as an argument to a SAS function via %SYSFUNC() you don't need to use %EVAL() to do the calculations before hand.

338   %let x1=%sysfunc(week(%eval(%sysfunc(today())-7)),z2.);
339   %let x2=%sysfunc(week(%sysfunc(today())-7),z2.);
340
341   %put &=x1 &=x2;
X1=10 X2=10

A NUMERIC argument that is.

346 %put |%sysfunc(putn(5+6,Z5.))|; |00011| 347 %put |%sysfunc(putc(5+6,$5.-R))|; | 5+6|

Re: Date in macro variables

Kurt_Bremser — Mon, 14 Mar 2022 14:26:20 GMT

@Tom wrote:
@Kurt_Bremser wrote:

Subtract 7 from the date. To make (integer) calculations in macro language, use the %EVAL function:
%let d=%sysfunc(week(%eval(%sysfunc(today())-7)),z2.);
Note that if the expression is being passed as an argument to a SAS function via %SYSFUNC() you don't need to use %EVAL() to do the calculations before hand.
338   %let x1=%sysfunc(week(%eval(%sysfunc(today())-7)),z2.);
339   %let x2=%sysfunc(week(%sysfunc(today())-7),z2.);
340
341   %put &=x1 &=x2;
X1=10 X2=10
A NUMERIC argument that is.
346 %put |%sysfunc(putn(5+6,Z5.))|; |00011| 347 %put |%sysfunc(putc(5+6,$5.-R))|; | 5+6|

And - tadaa! - once again I learned something new here.