BookmarkSubscribeRSS Feed
RobertNYC
Obsidian | Level 7

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
               _____
               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!

3 REPLIES 3
Reeza
Super User

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.

 

OR

 

1. Use DATE9 format

2. When using macro variable use it as "&macro_variable"D 

ballardw
Super User

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.

Tom
Super User Tom
Super User

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

sasdate=input(&sql_date,yymmdd10.) ;

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
  • 3 replies
  • 2404 views
  • 0 likes
  • 4 in conversation