07-26-2017 12:06 PM
I have a series of date range macro variables which I define at the beginning of a program.
%let high_date = '22JUL2017'd; data _null_; call symput('START_DATE',put(&high_date.-1095,date8.)); call symput('END_DATE',put(&high_date.,date8.));run;
The date range macro vars have to be defined this way in order to use them when running a query on an Oracle database through the SAS environment.
Later on in the program, I have to use these macro values in a SAS date time format for queries which have lines of proc sql code which look like this...
when max(datepart(TRANSACTION_DATE)) between &START_DATE. and &END_DATE. then 1 end as Customer_Status
I recive this error when I try to run lines of proc sql code like the above.
59 ! and); NOTE: Line generated by the macro variable "START_DATE". 59 23JUL14 _____ 22 76 ERROR 22-322: Syntax error, expecting one of the following: !!, *, **, +, -, /, AND, ||. ERROR 76-322: Syntax error, statement will be ignored.
I am having trouble figuring out how to change these date macro variables into SAS date time values so they can be used in a proc sql query.
data want; set have; sasdate=input(&START_DATE.,date8.) ; run;
I have been trying variations of the code below but no luck.
Between input(&START_DATE.,date8.) and input(&END_DATE_ACT.,date8.)
Any assistance will be greatly appreciated. Thank so much!
07-26-2017 12:33 PM
1. Use CALL SYMPUTX instead of CALL SYMPUT
2. Remove the formats (date8?????) from the date variable so that it's a SAS date which is a number, more specifically the number of days from Jan 1, 1960.
3. Leave rest of code as is.
1. Use DATE9 format
2. When using macro variable use it as "¯o_variable"D
07-26-2017 01:11 PM
Since Oracle and SAS do "dates" differently perhaps generate two sets of variables, one for when you must talk to Oracle and the other for use inside SAS such as @Reeza suggests.
07-26-2017 05:04 PM - edited 07-26-2017 05:06 PM
Why did you use DATE8. format? That does not make any sense. You can use DATE9 format and have room for four digit years. Or could use DATE7 format and only use two digit years but you wll have trouble with historical dates or birthdates which might not be in the 2000's.
If you want to use a macro variable with a bare DATE9 formatted date string inside SAS code then either add quotes to make it look like a string literal.
You can also add D to make it look like a date literal.
if mydate <= "&START_DATE"d ....
If you want it to look like a datetime literal then also add a time specification and use DT suffix.
if mytimestamp <= "&START_DATE:00:00"dt ....
If you want to put it in a different format so that it works in pass through code sent to a remote database then you might want to either keep seperate variables or use the INPUT() function call to convert the value to a date value. For example if you needed the value inside single quotes and in YYYY-MM-DD format (like '2017-07-26') then you might use this to create the macro variable.
data _null_; call symputx('sql_date',quote(put(date(),yymmdd10.),"'")); run;
Then if you wanted to use it in SAS code you could use the INPUT() function. No need to add quotes since they are already part of the macro varible.