call a macro inside proc sql

[basic_sas]

Here I define:

%let first_day=%sysfunc(intnx(month, %sysfunc(today()),-1,b), yymmdd10.);
%let last_day=%sysfunc(intnx(month, %sysfunc(today()),-1,e), yymmdd10.);
%put &first_day;
%put &last_day;

PROC SQL;

This is working...it's giving me the first day of last month and the last day of last month. 

I then try to call it and I'm just getting &first_day and &last_day in the log instead of the dates

WHERE pfs.fill_sold_dt BETWEEN &first_day AND &last_day

I have tried "&first_day"d and "&first_day"d.

[PaigeMiller]

Welcome @basic_sas

Macro variables for use in any type of analysis or comparisons should NOT be formatted. (This is not true if you want a macro variable for a title or label, then you want it formatted).

So, these macro variables ought to work in your PROC SQL.

%let first_day=%sysfunc(intnx(month, %sysfunc(today()),-1,b));
%let last_day=%sysfunc(intnx(month, %sysfunc(today()),-1,e));

Also, let's clean up the terminology to avoid confusion in the future. You are not calling a macro inside of PROC SQL. You have not created a macro. These are macro variables. You have created two macro variables.

[novinosrin]

Try:

%let first_day=%sysfunc(intnx(month, %sysfunc(today()),-1,b), date9.);
%let last_day=%sysfunc(intnx(month, %sysfunc(today()),-1,e), date9.);
%put &first_day;
%put &last_day;

WHERE pfs.fill_sold_dt BETWEEN "&first_day"d AND "&last_day"d

Basically the correction is using date9. format instead of yymmdd10. in your macro assignment statements 

[RW9]

Macro variables are just text.  You have asked for, the difference in months between today and -1, in format yymmdd10.  You cannot use this format with the d extension, you would need:

%let first_day=%sysfunc(intnx(month, %sysfunc(today()),-1,b), date9.);

...
where pfs.fill_sold_dt between "&first_day."d and "&last_day."d;

Note how I put the text macro variables in double quotes so they resolve, and finish them with decimal places, and then put d afterwards.  Also note how I don't code in upper case!

[PaigeMiller]

@RW9 and @novinosrin

It seems unnecessary extra work to me to format the values and then use the syntax "&macrovariable"d to un-format them into actual numbers which PROC SQL needs.

[novinosrin]

actually you are right. Just keep as nums. I fully agree Well well,starb coffee isn't powerful enough, Thank you for the catch. 

[basic_sas]

How would I do this so that the dates that I am looking for come out as 

2018-11-01 and 2018-11-30

I specifically need the format to be like that

[PaigeMiller]

---

@basic_sas wrote:

How would I do this so that the dates that I am looking for come out as 

2018-11-01 and 2018-11-30

I specifically need the format to be like that

---

No, you do not want the dates to be formatted like that if you are going to be using them in PROC SQL. You want SAS date values, which are integers. in fact, dates formatted like that WILL NOT WORK in PROC SQL.

[basic_sas]

Huh, well I have the query working when I just put in the dates that way with no macro variables. Otherwise, it can't find these numerical values for dates.

[PaigeMiller]

---

@basic_sas wrote:

Huh, well I have the query working when I just put in the dates that way with no macro variables. Otherwise, it can't find these numerical values for dates.

---

I'm skeptical. Show us an example of this working without macro variables. 

[Reeza]

---

@basic_sas wrote:

Huh, well I have the query working when I just put in the dates that way with no macro variables. Otherwise, it can't find these numerical values for dates.

---

You're likely using SQL Pass through then, but nothing in your code or post would have allowed us to know this ahead of time. 

If that is the case, you wouldn't actually be putting the dates as is, it would be in quotes, which you still need to do. I'm not sure if it needs single or double quotes, but it needs one and no d at the end.