DATA Step, Macro, Functions and more

Changing Character Macro Date Vars into SAS date time

Frequent Contributor
Posts: 103

Changing Character Macro Date Vars into SAS date time

Hi all,

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
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.) ;

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!

Super User
Posts: 23,724

Re: Changing Character Macro Date Vars into SAS date time

Posted in reply to RobertNYC


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 "&macro_variable"D 

Super User
Posts: 13,542

Re: Changing Character Macro Date Vars into SAS date time

Posted in reply to RobertNYC

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.

Super User
Super User
Posts: 8,108

Re: Changing Character Macro Date Vars into SAS date time

[ Edited ]
Posted in reply to RobertNYC

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.

sasdate=input("&START_DATE",date9.) ;

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.),"'"));

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.

sasdate=input(&sql_date,yymmdd10.) ;
Ask a Question
Discussion stats
  • 3 replies
  • 4 in conversation