topic Re: macro varible (date) in proc sql in New SAS User

macro date

rajeshm — Thu, 17 Sep 2020 16:50:29 GMT


%let pre_first_date = %str(%')%sysfunc(putn(%sysfunc(intnx(month, %sysfunc(today()), -1, begin)), date9.))%str(%');
%let pre_last_date = %str(%')%sysfunc(putn(%sysfunc(intnx(month, %sysfunc(today()), -1, end)), date9.))%str(%');
%let d_pre_first_date =%str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1)),date9.))%str(%')d;
%let d_pre_last_date =%str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1,end)),date9.))%str(%')d;
%put &pre_first_date &d_pre_last_date;
proc sql;
create table xxx as
select
zz
from mytablexxx
/* option1 where po_date between &d_pre_first_date and &d_pre_last_date;--not working*/
/*option2 where po_date between "&d_pre_first_date." and "&d_pre_last_date.";ERROR: Expression using IN has components that are of different data types.*/
where po_date between "&pre_first_date."d and "&pre_last_date."d;/* working fine */
quit;

I am so sorry for posting partial data in my earlier mail,plsconsider that let me know why first and second options are not working.

Re: macro date

Kurt_Bremser — Thu, 17 Sep 2020 15:07:09 GMT

You are overcomplicating things by miles, for no reason at all.

See Maxim 28: Macro Variables Need No Format.

%let pre_first_date = %sysfunc(intnx(month,%sysfunc(today()),-1,begin));
%let d_pre_last_date = %sysfunc(intnx(month,%sysfunc(today()),-1,end));

where post_date between &pre_first_date. and &d_pre_last_date.;

Re: macro date

PaigeMiller — Thu, 17 Sep 2020 15:09:26 GMT

You are working very hard to perform tasks that should be avoided, and also violates Maxim 28. Macro variables should not be formatted!

Thus, you want

%let pre_first_date = %sysfunc(intnx(month, %sysfunc(today()), -1, begin));
%let d_pre_last_date =%sysfunc(intnx(month,%sysfunc(today()),-1,end));

and then

where post_date between &pre_first_date and &d_pre_last_date

Much simpler!

(The only time you need to format macro variables is in titles or labels, but macro variables should not be formatted in arithmetic or boolean expressions).

Re: macro date

Kurt_Bremser — Thu, 17 Sep 2020 15:13:29 GMT

Your problem was exactly this: text replacement. Insert the created macro variables within the double quotes, and you get

"'01AUG2020'"d

and

"'31AUG2020'd"d

which both are, very obviously, not SAS date literals.

Re: macro date

ballardw — Thu, 17 Sep 2020 15:16:42 GMT

First lets discuss what youi think the variable

&pre_first_date.

Should look like.

Then run this and look in the log:

%put &pre_first_date.;

Which will show something like

'01AUG2020'

So when you use

where post_date between "&pre_first_date."d

When the macro variable resolves generates

where post_date between "'01AUG2020'"d

Hint: there are too many quotes.

So remove your forced quotes in the macro variable. Unless you are using the same variable to also create human readable text like a title or the name of an output file you would be better off with

%let pre_first_date = %sysfunc(intnx(month, %sysfunc(today()), -1, begin));
/* same for the other bound*/
/* and use*/
where post_date between &pre_first_date. and &d_pre_last_date

macro varible (date) in proc sql

rajeshm — Mon, 21 Sep 2020 09:33:46 GMT

this is similar to my previous code but no one replied after edit my post.

how to avoid hardcoding d in this "&pre_first_date."d


%let pre_first_date = %str(%')%sysfunc(putn(%sysfunc(intnx(month, %sysfunc(today()), -1, begin)), date9.))%str(%');
%let pre_last_date = %str(%')%sysfunc(putn(%sysfunc(intnx(month, %sysfunc(today()), -1, end)), date9.))%str(%');
%let d_pre_first_date =%str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1)),date9.))%str(%')d;
%let d_pre_last_date =%str(%')%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1,end)),date9.))%str(%')d;
%put &pre_first_date &d_pre_last_date;
proc sql;
create table xxx as
select
zz
from mytablexxx
/* option1 where po_date between &d_pre_first_date and &d_pre_last_date;--not working*/
/*option2 where po_date between "&d_pre_first_date." and "&d_pre_last_date.";ERROR: Expression using IN has components that are of different data types.*/
where po_date between "&pre_first_date."d and "&pre_last_date."d;/* working fine but how can i avoid hardcoding d here */
quit;

Re: macro varible (date) in proc sql

PaigeMiller — Mon, 21 Sep 2020 10:54:58 GMT

Hello, @rajeshm , we discussed this and answered your question in your thread last week at https://communities.sas.com/t5/New-SAS-User/macro-date/m-p/684647

DO NOT FORMAT MACRO VARIABLES

Re: macro varible (date) in proc sql

Kurt_Bremser — Mon, 21 Sep 2020 11:14:10 GMT

I merged this back into the original thread dealing with the same question.

The answer stays: do not format macro variables.