Help with macro variables

[rechavarri]

I’m trying to set a macro date variable and use that variable within a sql statement but I can’t seem to get the syntax right.

Example:

%let end_date = %sysfunc(today(),mmddyy10.);
%let beg_date = &end_date – 60;

Select column1
From table1
Where datecolumn between &beg_date and &end_date

The end_date variable is set to the current system date and that works fine but I can’t seem to set the second variable to be 60 days prior to the current date. I’ve tried many different variations and I can’t figure it out.

I can do this within a data step but It doesn’t work with a macro variable.

[Cynthia_sas]

Hi:
This is not an ODS question.

In general, when working with SAS Macro variables, it is a good idea to have a working SAS program BEFORE you convert the program to use SAS macro variables.

For example, given this data file:
[pre]
data table1;
infile datalines;
input datecolumn : mmddyy10. column1;
return;
datalines;
03/01/2007 1111
04/15/2007 2222
05/16/2007 3333
06/18/2007 4444
07/04/2007 5555
;
run;

ods listing;
proc print data=table1;
title 'note how dates are internally stored as numbers';
run;
[/pre]
No matter HOW the date value is read into SAS, note how the datecolumn variable is represented in PROC PRINT without any formatting -- date values are just the number of days from Jan 1, 1960 (the zero date in SAS):
[pre]
note how dates are internally stored as numbers

Obs datecolumn column1

1 17226 1111
2 17271 2222
3 17302 3333
4 17335 4444
5 17351 5555
[/pre]
So, today's date is 17335 and 60 days before today is 17275 (17335 - 60). If I had used a format statement in the PROC PRINT, the date variable would STILL be a number internally, SAS just would have displayed that number in a readable date format.

Once you know that the date is stored as a number internally, then you can build the correct SQL WHERE clause to do the selection. Consider these 4 SQL queries -- each one is attempting to run against TABLE1 -- using a different form for the date value. Only 2 of these queries will work:
[pre]
** this query will work;
title '1: Use a number that represents the date';

proc sql;
Select datecolumn format=mmddyy10. , column1
From table1
Where datecolumn between 17275 and 17335;
quit;

** this query will work;
title '2: Use a date constant in the WHERE clause';

proc sql;
Select datecolumn format=mmddyy10. , column1
From table1
Where datecolumn between "19Apr2007"d and "18jun2007"d;
quit;

** this query will not work because it is not the right;
** form for a SAS date constant;

title '3: Try to use "mm/dd/yyyy"d as a date constant in the WHERE clause';
proc sql;
Select datecolumn format=mmddyy10. , column1
From table1
Where datecolumn between "04/19/2007"d and "06/18/2007"d;
quit;

** this query will not work because the internal value;
** for datecolumn is a NUMBER and the dates as shown below;
** are invalid in the query;
title '4: Try to use mmddyy10 form in the WHERE clause';

proc sql;
Select datecolumn format=mmddyy10. , column1
From table1
Where datecolumn between 04/19/2007 and 06/18/2007;
quit;

[/pre]

When you use macro variables, you are creating TEXT strings. So, when you are manipulating the date variables as macro variables, what you are doing here:
%let beg_date = &end_date – 60;
is the equivalent of trying to do this: "06/18/2007" - 60
Even if 06 divided by 18 divided by 2007 would evaluate in the %LET statement, it would NOT be a date value anymore.

For more help with this code and macro variable reference issues, your best bet is to contact Tech Support.

cynthia

[PeterMehnert]

If you evaluate arithmetic or logical expressions you have to use the macro functions %EVAL for integer and %SYSEVALF for floating-point arithmetic.

In this case your example would look like

%let end_date = %sysfunc(today(),5.);
%let beg_date = %eval(&end_date - 60);

If datecolumn is a SAS Date Variable your SQL should work.

regards, Peter