topic Re: Date macro inside proc sql in SAS Programming

Date macro inside proc sql

elessar — Tue, 12 Mar 2019 07:49:28 GMT

Let we have macro which are calculating by proc sql and are defining through "into":

data calendar;
format dt date9.;
	flg=0;	dt=today();	output;
	flg=1;	dt=today()-1;	output;
	flg=1;	dt=today()-2;	output;
	flg=0;	dt=today()-3;	output;
	flg=0;	dt=today()-4;	output;
	flg=0;	dt=today()-5;	output;
run;

proc sql;
create table t1 as
	select max(dt) as max_dt format=date9. 
	into :max_dt
	from calendar
	where dt < today() and flg = 0
;quit;

%put &max_dt;

But when I try to use it in select of second proc sql (this is absurd code, just for example), there is an error:

proc sql;
create table t2 as
	select &max_dt. as last_work_dt
	from calendar
	where dt = today()-3
;quit;

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, 
              a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER.  

ERROR 22-322: Syntax error, expecting one of the following: a name, *.

How can I use this date macro inside second proc sql?

Re: Date macro inside proc sql

s_lassen — Tue, 12 Mar 2019 08:37:57 GMT

The macro variable max_dt will contain a string like 12MAR2019. To use that as a date constant in SAS, use "&max_dt"d, so you get a date constant, like "12MAR2019"d.

Re: Date macro inside proc sql

RW9 — Tue, 12 Mar 2019 08:48:56 GMT

It highlights one of the primary reasons why you should not keep data in macro variables. Macro is a text find and replace system it only deals with text and the generation of text. So when the text of your code is going through its checks it finds:

select &max_dt. as last_work_dt

&max_dt. resolves to 12MAR2019, so your code line looks like;

select 12MAR2019 as last_work_dt

Which you can see is incorrect code. The above find/replace debugging works on all macro code, as the above is all that macro code does. Datastep processing of datasets has all the different types, collections, functions etc.

Re: Date macro inside proc sql

elessar — Tue, 12 Mar 2019 09:34:15 GMT

How would it be better for change the code? Your option still give an error:

proc sql;
create table t2 as
	select "&max_dt."d as last_work_dt
	from calendar
	where dt = today()-3
;quit;

ERROR: Invalid date/time/datetime constant "&max_dt."d.

Re: Date macro inside proc sql

Kurt_Bremser — Tue, 12 Mar 2019 09:42:05 GMT

Please post the whole log of the step. I guess you also get a message

WARNING: Apparent symbolic reference MAX_DT not resolved.

Re: Date macro inside proc sql

elessar — Tue, 12 Mar 2019 09:44:56 GMT

But I can do that this way:

%let random_dt = today();

proc sql;
create table t3 as
	select &random_dt. format=date9. as last_work_dt
	from calendar
	where dt = today()-3
;quit;

or this:

%let random_dt = %unquote('12mar2019'd);

proc sql;
create table t3 as
	select &random_dt. format=date9. as last_work_dt
	from calendar
	where dt = today()-3
;quit;

and both options work. What the difference between that and macro's definition through "into"?

Re: Date macro inside proc sql

Kurt_Bremser — Tue, 12 Mar 2019 10:00:34 GMT

There is a magical thing called the %put macro statement, and you should use it for debbugging:

%let random_dt = %unquote('12mar2019'd);

%put &random_dt.;

log:

24         %let random_dt = %unquote('12mar2019'd);
25         
26         %put &random_dt.;
'12mar2019'd

You can see that the %unquote does NOT remove the single quotes, and the date literal stays a valid date literal.

Your first code:

%let random_dt = today();

assigns the TEXT(!)

today()

(not a value!) to the macro variable, and so that function call is inserted wherever you use the macro variable.

As @RW9 already said, the macro preprocessor is just an advanced text replacement engine, and you have to "get" that before you'll be able to successfully use it.

In your initial select into, you made the mistake of formatting the date value with date9, which is unnecessary and impedes further use of the macro variable by requring to enclose it in ""d. See Maxim 28.

Re: Date macro inside proc sql

RW9 — Tue, 12 Mar 2019 10:11:56 GMT

Step 1 to learning Macro, turn on the options:

options mlogic mprint sybolgen source source2;

You can turn it back off with:

options nomlogic nomprint nosybolgen nosource nosource2;

These options present to the log more information about the process which is happening within the macro pre-processor, you can see what macro variables contain, what logic is executed. If you review the variables and what they contain, and then do the find/replace yourself, you will see what is valid code and what is not. Remember, macro is nothing more than a find/replace system.

Re: Date macro inside proc sql

PaigeMiller — Tue, 12 Mar 2019 10:46:09 GMT

@elessar wrote:

But I can do that this way:
%let random_dt = today();

proc sql;
create table t3 as
	select &random_dt. format=date9. as last_work_dt
	from calendar
	where dt = today()-3
;quit;
or this:
%let random_dt = %unquote('12mar2019'd);

proc sql;
create table t3 as
	select &random_dt. format=date9. as last_work_dt
	from calendar
	where dt = today()-3
;quit;
and both options work. What the difference between that and macro's definition through "into"?

In both of these, you are not passing a formatted date into the macro variable. In both of these you are working with an un-formatted SAS date value, which PROC SQL understands. PROC SQL does not understand a formatted date value unless you enclose it in quotes with a d on the end, such as "&random_dt"d

There is no need to format SAS date or datetime or time values when you put them into macro variable. This was the cause of your original problems.

Also, people have suggested you use certain options when you work with macros, and also the %PUT statement to see the values contained in macro variables, these are highly recommended!