macro date

[rajeshm]

%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.

[Kurt_Bremser]

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.;

[PaigeMiller]

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).

[Kurt_Bremser]

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.

[ballardw]

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 

[rajeshm]

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;

[PaigeMiller]

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

[Kurt_Bremser]

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

The answer stays: do not format macro variables.