BookmarkSubscribeRSS Feed
ThomasH
Calcite | Level 5
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
5 REPLIES 5
DanielSantos
Barite | Level 11
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.
ThomasH
Calcite | Level 5
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
DanielSantos
Barite | Level 11
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.
ThomasH
Calcite | Level 5
Well, guess I was thinking too complicated - that worked, thanks a lot!

Thomas Message was edited by: ThomasH
Cynthia_sas
SAS Super FREQ
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 834 views
  • 0 likes
  • 3 in conversation