Help using Base SAS procedures

Using date macro variable in SQL procedure

Accepted Solution Solved
Reply
Regular Contributor
Posts: 186
Accepted Solution

Using date macro variable in SQL procedure

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.


Accepted Solutions
Solution
‎12-10-2012 01:47 PM
Super User
Posts: 17,829

Re: Using date macro variable in SQL procedure

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

View solution in original post


All Replies
Super User
Posts: 10,500

Re: Using date macro variable in SQL procedure

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?

Regular Contributor
Posts: 186

Re: Using date macro variable in SQL procedure

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.

Solution
‎12-10-2012 01:47 PM
Super User
Posts: 17,829

Re: Using date macro variable in SQL procedure

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

Regular Contributor
Posts: 186

Re: Using date macro variable in SQL procedure

Exactly what I was looking for.

Works great.

Thank you very much.

PROC Star
Posts: 7,363

Re: Using date macro variable in SQL procedure

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 4784 views
  • 7 likes
  • 4 in conversation