topic Re: Macro variable in SQL in SAS Programming

Macro variable in SQL

ThomasH — Tue, 14 Jul 2009 08:55:12 GMT

Hi all,

how would I use a macro variable, which represents an integer value of a date (e.g. 17889) in a comparison with a DATE9. value?

e.g.

PROC SQL;

CREATE TABLE work.test AS
SELECT a, b
FROM c
WHERE
d < &_macrovariable;

QUIT;

Thanks a lot,
Thomas Message was edited by: ThomasH

Re: Macro variable in SQL

DanielSantos — Tue, 14 Jul 2009 09:14:10 GMT

Hello Thomas.

17889 is exactly the same as '23DEC2008'd

Only difference is that the latest has a DATE9. format applied to it, but really its just a number.
Now, if you wish to assign a date value with the DATE9. notation you should enclose your date between quotes and the use the d suffix. In this form, SAS will read the formatted value and convert it to a numeric value, for calculations.

If you run the code above:

data _null_;
X=17889;
Y='23DEC2008'd;
put X date9.;
put Y 5.;
run;

The result will be the following:

23DEC2008
17889

Check the online SAS documentation for dealing with date values:
http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/a001304321.htm

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

Re: Macro variable in SQL

ThomasH — Tue, 14 Jul 2009 09:18:07 GMT

Hi Daniel,

thanks for your quick reply.

Of course I tried this notation already but it doesnt work with "&_macrovariable"d either.

Any other ideas? I need to use it in a PROC SQL and not in a data step.

Thanks,
Thomas Message was edited by: ThomasH

Re: Macro variable in SQL

DanielSantos — Tue, 14 Jul 2009 09:23:37 GMT

OK then...

%let _macrovariable=23DEC2008;
...
WHERE d lt "&_macrovariable"d;

or

%let _macrovariable='23DEC2009'd;
...
WHERE d lt &_macrovariable;

or

%let _macrovariabe=17889;
...
WHERE d lt &_macrovariable;

Are all equivalent and should work.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

Re: Macro variable in SQL

Cynthia_sas — Tue, 14 Jul 2009 15:08:40 GMT

Hi:
That is very strange. "&macvar"d -should- work in a WHERE clause. See example below. As you can see below, you need to use quotes where appropriate. If I had these 2 where clauses:
[pre]
where bday = -3334;
OR
where bday = '15NOV1950'd;
[/pre]

and I was going to turn them into code that used a macro reference, the FIRST thing I'd have to do was realize that I could not use a macro variable in single quotes, so that second WHERE would change to:

[pre]
where bday = "15NOV1950"d;
[/pre]

NOW, I'm ready to create and use my macro variables:

%let usenum = -3334;
%let useconstant = 15NOV1950;

[pre]
working code
where bday = -3334;
becomes
where bday = &usenum;
AND
working code
where bday = '15NOV1950'd;
becomes
where bday = "&useconstant"d;
[/pre]

Note that the correct WORKING syntax determines the use of quotes or not. If I am going to use the internal date number (-3334), then the where clause does not need quotes. However, if I plan to use the string for bday (15Nov1950), then I have to put double quotes and a d (for date constant) in my where clause, so that the where clause successfully transforms the string into a date value. The quotes and the d belong to the -use- of the string for date in the WHERE clause. Nothing happens in the %LET statement if you use '15Nov1950'd as the value for a macro variable -- that is just another string, but with quotes and a d added to the string.

I disagree with those who recommend putting quotes in the macro variable:
%let macvar = '15Nov1950'd; -- generally, I find it is not a good practice to try to "pre-quote" macro variables -- because, it limits the ways I can use the macro variable if the quotes are already embedded in the macro variable value.

If you still find that "&macvar"d does not work in your where clause, perhaps you should open a track with Tech Support so they can look at your EXACT code and your data to see if they can help you figure out what's wrong.

cynthia

** the code to test;
ods listing;

data bday;
infile datalines;
input name $ bday : mmddyy10.;
format bday date9.;
return;
datalines;
drew 11/15/1950
kermit 05/09/1955
george 11/19/1966
sarah 11/29/1984
jim 05/24/1936
lou 11/15/1950
sue 11/15/1950
;
run;

%let usenum = -3334;
%let useconstant = 15Nov1950;

title '1) use internal number for date';
proc sql;
select *
from work.bday
where bday = - 3334;
quit;

title '2) use date constant for date';
proc sql;
select *
from work.bday
where bday = "15nov1950"d;
quit;

title '3) use macro variable with double quotes for date';
proc sql;
select *
from work.bday
where bday = "&useconstant"d;
quit;

title '4) use macro variable with date number';
proc sql;
select *
from work.bday
where bday = &usenum;
quit;

title '5) Use Function with internal number';
proc sql;
select *
from work.bday
where month(bday) = month(&usenum);
quit;

title '6) use function with date constant and double quotes';
proc sql;
select *
from work.bday
where month(bday) = month("&useconstant"d);
quit;

Re: Macro variable in SQL

ThomasH — Tue, 14 Jul 2009 16:37:51 GMT

Well, guess I was thinking too complicated - that worked, thanks a lot!

Thomas Message was edited by: ThomasH