Date Type Parameter in EG 4.1

[deleted_user]

I have created query that use macro variable theDate.
Here is condition:
where month(ValidFrom) = month(&theDate) and year(ValidFrom) = year(&theDate)

After, I have created Storage Process, which has parameter theDate of Date type.

When I run this STP, I select a date from calendar and log says:
121 + where month(ValidFrom) = month(&theDate) and year(ValidFrom) = year(&theDate);
NOTE: Line generated by the macro variable "THEDATE".
121 12Apr2007
_______
22
NOTE: Line generated by the macro variable "THEDATE".
121 12Apr2007
_______
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR,
^=, |, ||, ~=.
122 + QUIT;

I cannot understand what is the problem. theDate has a numeric value, so it must be ok.

[prholland]

Try changing the code to add "..."d around the dates:

where month(ValidFrom) = month("&theDate."d) and year(ValidFrom) = year("&theDate."d)

Internally SAS dates are numeric. However, SAS doesn't recognise 12Apr2007 as a date, but does if it is date-quoted as "12Apr2007"d.

........Phil

[Cynthia_sas]

Hi!
By the time the value for your macro variable hits the Macro Global Symbol Table, it is just a character string. As a Stored Process author, it is up to you to treat the macro variable appropriately. This is what the error message was telling you. The string "12Apr2007" is invalid with the month function. SAS is expecting to see a date constant or a variable whose value is the number of days since Jan 1, 1960 or an expression that resolves to a numeric date value. The date 12Apr2007 is the number 17268 -- so you either have to code your WHERE as:
[pre]
where month(ValidFrom) = month(17268) and year(ValidFrom) = year(17268)
[/pre]
OR
[pre]
where month(ValidFrom) = month("&thedate"d) and year(ValidFrom) = year("&thedate"d)
[/pre]

The first example is the numeric value for 12Apr2007 and the second example says to treat the value of &thedate (12Apr2007) as a date constant. You can duplicate the error message if you "hardcode" a WHERE clause that has this:
[pre]
where month(ValidFrom) = month(12Apr2007) and year(ValidFrom) = year(12Apr2007)
[/pre]

Good luck,
cynthia

[deleted_user]

Thank you very much. It was very helpful.
Please explain me why I need to put dot here year("&theDate."d).

[Cynthia_sas]

Hi:
You should NOT need to have a dot where you show it, although in my test, the dot doesn't break the code. Basically a dot is one way to tell the Macro processor that the end of a macro variable has been reached. So, for example, if I want to concatenate 2 macro variables:
[pre]
%let first = year;
%let second=2007;
%let both = &first.&second;
%put BOTH together is: &both;
[/pre]

Then the dot between the 2 macro variables tells the macro processor to only resolve &first -- because the dot signals the end of the macro variable name. If you needed a dot in a name (as in a data set reference), then you might do this:
[pre]
%let lib = sasuser;
%let table=class;
%let myfile1 = &lib.&table;
%let myfile2= &lib..&table;

proc print data=&myfile1; title 'wrong'; run;

proc print data=&myfile2; title 'right'; run;
[/pre]

Note the use of 2 dots in the %LET statement for &myfile2. The first proc print will get an error message, but the second proc print will work. The Macro facility has some rules that it follows when it's scanning and resolving macro variable references and if you're interested in reading about those rules, search in the SAS documentation for the topics:
"Using Macro Variables" and
"Referencing Macro Variables Indirectly".

In your case, the " after &theDate -- "&thedate"d should be OK to act as token that ends the macro variable. If you are getting this message:
[pre]
Beginning in SAS 9, users may receive the following message when the
DATA step compiler encounters a letter immediately after a closing
single or double quote:

NOTE 49-169: The meaning of an identifier after a quoted string may
change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier
is recommended.

This message is intended to warn users that additional literal types
may be added in the future, and inserting a blank space after a quoted
string ensures that programs continue to run without errors in future
releases of SAS.
[/pre]
as described here: http://support.sas.com/techsup/unotes/SN/003/003353.html

Since you ARE using a date constant, you can just ignore that NOTE. When I tested the WHERE statement, my version of the WHERE (inside a PROC PRINT) worked both with and without the dot.

I generally recommend starting with the simplest code that works, because in the long run, the simplest code will be the easier to understand and easier to maintain.

cynthia