BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nicnad
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

5 REPLIES 5
ballardw
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

Exactly what I was looking for.

Works great.

Thank you very much.

art297
Opal | Level 21

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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