BookmarkSubscribeRSS Feed
Gil_
Quartz | Level 8
I have a function that's is giving me a date range when I pull it I get and error

DATA _null; start_date=today()-2;
End_date=today ()-2;
If weekday (start_date)=1 then start_day=start_day-1;
Call symput ('dt', "'"||put (start_date, date.)||"'d'");
Call symput ('dt1', "'"||put (End_date, date.)||"'d'");

The orderdate is a datetime20 format
(ts '2017-04-18 00:00:00:000)
My where statement
Where al1.order_date between &dt. And &dt1.
Log
Macro variable dt resolves to '22apr17'd

Syntax error: expected something between a string or Unicode character literal and word 'd'



5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20

Im not following your question, the where statement and the orderdate your are referring to are not in your code? Post your entire code and some sample data if necesarry

Gil_
Quartz | Level 8
Hi thanks for responding ..the data null on top would give me the date I need since it's a minus -2 it will give me sat day ...The order_date is a datetime20 format
(ts '2017-04-18 00:00:00:000)
Where al1.order_date between &dt. And &dt1.
Astounding
PROC Star

Several issues to consider.

 

First, instead of using DATE. as the format, use DATE9. as the format.  That way your macro variable will resolve to '22apr2017'd (including a four-digit year is safer).

 

Second, you cannot accurately compare a datetime to a date.  They take on a different range of values.  If ORDER_DATE is truly a datetime, you could use:

 

Where datepart(al1.order_date) between &dt. And &dt1.

 

Third, it's not clear what is in ORDER_DATE (is it really a datetime, a character string, or something else entirely).  But if it's really a datetime, the combination of the earlier steps should work.

Kurt_Bremser
Super User

Aside from the fact that you are comparing date and datetime values, which will not work as expected, you do not need the date/datetime literal formatting in macro variables.

data _null_;
start_date = today() - 2;
end_date = today() - 2;
if weekday(start_date) = 1 then start_day = start_day - 1;
call symput('dt',put(start_date,best.));
call symput('dt1',put(end_date,best.));
run;

stores the raw date values as simple numbers, which are semantically the same. And it avoids all the hassles with the quotes.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Whilst your question has been answered, I am going to ask you this question.  You have logic to determine start and end date, so why are you going through the process of creating macro variables of the resolved logic, then using that in your program.  This is simple using macro for the sake of using macro, it adds nothing but complication to an otherwise simple statement.  This all derives down to:

where a1.order_date = today() - 2;

(assumes order date is numeric).  So why do you have all that extra code doing absolutely nothing?  Even if end date is different to start (which it isn't in your example):

where a1_order_date between today() -2 and today() + 2;

Still doesn't need all the faff.

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
  • 5 replies
  • 1227 views
  • 0 likes
  • 5 in conversation