topic Re: Using date macro variable in SQL procedure in SAS Procedures

Using date macro variable in SQL procedure

nicnad — Mon, 10 Dec 2012 15:31:53 GMT

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 — Mon, 10 Dec 2012 16:23:26 GMT

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 — Mon, 10 Dec 2012 18:44:36 GMT

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 — Mon, 10 Dec 2012 18:47:59 GMT

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 — Mon, 10 Dec 2012 22:13:53 GMT

Exactly what I was looking for.

Works great.

Thank you very much.

art297 — Mon, 10 Dec 2012 23:59:01 GMT

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