Using date macro variable in SQL procedure

[nicnad]

Hi,

Here is the code I generally use this SQL procedure to create the table I want :

PROC SQL;
CREATE TABLE SASUSER.test AS
SELECT
    PFC.NOTRANSIT AS TRANSIT
FROM   testlib.LST_CL AS PFC
    PFC.MOIS = '31OCT2012:00:00:00'dt
;QUIT;

Now since the month is always the latest month in the table I tought of adding the max date of this table in a variable :

proc sql noprint;  
select max(MOIS)
into : max_date
from testlib.LST_CL AS PFC; 
quit;

Now how do I use this variable in my SQL procedure? The following gives me an error :

PROC SQL;
CREATE TABLE SASUSER.test2 AS
SELECT
    PFC.NOTRANSIT AS TRANSIT
FROM   testlib.LST_CL AS PFC
    PFC.MOIS = &max_date

;QUIT;

What is the proper way to do this?

Thank you for your help and time.

[ballardw]

What type of values are in MOIS, SAS Date, SAS datetime, string?

What kind of result do you get from:

%put &max_date; when run after the first bit of Proc SQL code?

[nicnad]

Thank you for the quick reply.

Sorry if my original post missed detail.

Here is the result of %put &max_date :

%put &max_date;

31OCT2012:00:00:00

Not sure if this is recognized as SAS datetime or string.

Hope you can help me with this.

Thank you for your help and time.

[Reeza · Accepted solution]

Macro variables are all characters so you need to enclose the macro variable in quotes and add the literal at the end.

   PFC.MOIS = "&max_date"dt

[nicnad]

Exactly what I was looking for.

Works great.

Thank you very much.

[art297]

Just always ensure to enclose them in double quotes or else the macro variable won't resolve.