BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
avatar
Fluorite | Level 6

I created below macro  

 

options symbolgen;
%let T1 = %sysfunc(intnx(Day,"&sysdate"d,-9),mmddyy10);
%let T2 = %sysfunc(intnx(Day,"&sysdate"d,-3),mmddyy10);

 

to bring records for previous week from oracle database ( has millions of records)

 

Proc sql ;

where datepart(a.trans_dt) >= &T1 AND datepart(a.trans_dt) <= &T2
order by a.trans_dt;

 

brings zero rows.

trans_dt  is datetime variable. 

 

How do I resolve? Please help

 

Thanks

 

we are using EG5.1  / SAS 9.1

 

if I use 

where (datepart(a.trans_dt) >= intnx('week',today(),-1,'b')
AND datepart(a.trans_dt) <=intnx('week',today(),-1,'e')); I get 64 rows but takes 2 hrs  --  Hope it helps to understand 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

@avatar wrote:
Tried with datepart and got zero rows. Not sure which one to use . Please advise.
AND datepart(a.trans_dt >="&T1"d) and datepart(a.trans_dt <="&T2"d))

You must not use the inequality as the argument of the DATEPART function. This would mean datepart(1) or datepart(0) (depending on whether the inequality is true or false), both of which are 0, i.e. FALSE (as a Boolean value).

 

AND datepart(a.trans_dt) >="&T1"d and datepart(a.trans_dt) <="&T2"d

The above line would make sense logically, but only if a.trans_dt contains SAS datetime values, which has still not been verified. Not trial and error, but an examination of the unformatted values of variable TRANS_DT should clarify this: Just select TRANS_DT from the database and apply PROC MEANS to this variable.

 

 [Edit: improved wording]

View solution in original post

14 REPLIES 14
Reeza
Super User

 

Remove the format from the macro code. 

 

Currently your macro variables look like mmddyy10, but that wouldn't work in the query. 

 

where datepart(a.trans_dt) >= &T1 AND datepart(a.trans_dt) <= &T2 

Becomes the following, which isn't valid SAS code. 

 

where datepart(a.trans_dt) >= 04/16/2016
 AND datepart(a.trans_dt) <= 04/22/2016
 

Instead use the following which creates SAS date values as the number of days since Jan 1, 1960. If you really want to see the date format use date9 and then add quotes and the letter do to the values in your Where clause ("&t2"d).

 

%let T1 = %sysfunc(intnx(Day,"&sysdate"d,-9));
%let T2 = %sysfunc(intnx(Day,"&sysdate"d,-3));
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Put your "data" in a dataset and then use that.  Macro code is for generating SAS code, not for processing data&colon;

data dates;
  lower=intnx('day',"&sysdate."d,-9);
  upper=intnx('day',"&sysdate."d,-3);
run;

proc sql;
  ...
  from ORACLE_DATABASE
  where  (select LOWER from DATES) <= datepart(TRANS_DATE) <= (select UPPER from DATES);
quit;
avatar
Fluorite | Level 6

yes. I didnot get results from previous code. I Will give this a try

 

thanks  

avatar
Fluorite | Level 6
Got this error:

AND datepart(a.trans_dt) >= "&T1"d
SYMBOLGEN: Macro variable T1 resolves to 20562
ERROR: Invalid date/time/datetime constant "20562"d.
75 AND datepart(a.trans_dt) <= "&T2"d)
SYMBOLGEN: Macro variable T2 resolves to 20568
ERROR: Invalid date/time/datetime constant "20568"d.
76 order by a.trans_dt;
avatar
Fluorite | Level 6

%let START_DATE ='17APR2016'd;
%let END_DATE ='23APR2016'd;

 

proc sql;

 

.....

where (a.trans_dt >=&START_DATE.) and (a.trans_dt <=&END_DATE. )

 

this works. but I want run for previous week from today. any other solution?

 

 

Reeza
Super User

Either of these will work.

 

 

%let T1 = %sysfunc(intnx(Day,"&sysdate"d,-9));
%let T2 = %sysfunc(intnx(Day,"&sysdate"d,-3));

 

 

 

 

Keep your where clause as originally designed, T1/T2

 

where (a.trans_dt >=&T1.) and (a.trans_dt <=&T2. )

 

Or, if you really insist:

%let T1 = %sysfunc(intnx(Day,"&sysdate"d,-9), date9.);
%let T2 = %sysfunc(intnx(Day,"&sysdate"d,-3), date9.);

And your where clause becomes:

 

where (a.trans_dt >="&T1"d) and (a.trans_dt <="&T2"d)
avatar
Fluorite | Level 6
AND (a.trans_dt) >= &T1. AND (a.trans_dt) <= &T2.
SYMBOLGEN: Macro variable T1 resolves to 20562
SYMBOLGEN: Macro variable T2 resolves to 20568
79 order by a.trans_dt;
NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
NOTE: Table WORK.SPARCS_RETURN created, with 0 rows and 29 columns.

Reeza
Super User

Sorry, you still need the datepart in your where clause. It was there in your original question, but removed later on for some reason?

AND datepart(a.trans_dt) >= &T1. AND datepart(a.trans_dt) <= &T2.

 

If you go the other route (date9) you need the datepart as well. 

 

EDIT:

Your query before shows it works with date9 format and no datepart so I don't know. Maybe the dates created aren't what you need? Try using the date9 format and see what dates are created vs what you need, maybe the intnx needs to change.

avatar
Fluorite | Level 6
Actually ,It worked without datepart in
(a.trans_dt) >="&T1"d AND (a.trans_dt) <="&T2"d
If I use datepart ---- keeps running no results
FreelanceReinh
Jade | Level 19

@avatar wrote:
Actually ,It worked without datepart in
(a.trans_dt) >="&T1"d AND (a.trans_dt) <="&T2"d
If I use datepart ---- keeps running no results

If the criterion without "datepart" is satisfied by one or more observations, it is virtually certain that a.trans_dt is not a SAS datetime value (contrary to what the statement "trans_dt  is datetime variable" in your initial post suggested). So, I would examine what the trans_dt values really are.

 

Looking at the log excerpt in your previous post, containing the line

AND (a.trans_dt) >= &T1. AND (a.trans_dt) <= &T2.

and indicating a result of "0 rows," I'm wondering what (restrictive?) condition you applied before the first "AND."

avatar
Fluorite | Level 6
AND (a.trans_dt >="&T1"d) and (a.trans_dt <="&T2"d)
SYMBOLGEN: Macro variable T1 resolves to 17APR2016
SYMBOLGEN: Macro variable T2 resolves to 23APR2016
74 order by a.trans_dt;

TRANS_DT in the report has datetime values
18APR2016:13:04:02
19APR2016:06:58:14
19APR2016:07:01:50
19APR2016:07:03:54
20APR2016:07:40:59
21APR2016:09:59:10
21APR2016:14:39:43
22APR2016:11:06:25
FreelanceReinh
Jade | Level 19

Still, if TRANS_DT contained these datetimes as SAS datetime values, these would be numbers >1000000000. On the other hand, '23APR2016'd=20567. So, the inequality a.trans_dt <="&T2"d would not hold.

 

I would make sure that these inequalities are evaluated as expected, looking at unformatted values.

avatar
Fluorite | Level 6
Tried with datepart and got zero rows. Not sure which one to use . Please advise.
AND datepart(a.trans_dt >="&T1"d) and datepart(a.trans_dt <="&T2"d))
SYMBOLGEN: Macro variable T1 resolves to 17APR2016
SYMBOLGEN: Macro variable T2 resolves to 23APR2016
74 order by a.trans_dt;
NOTE: Table WORK.SPARCS_RETURN created, with 0 rows and 29 columns.

75 QUIT;
FreelanceReinh
Jade | Level 19

@avatar wrote:
Tried with datepart and got zero rows. Not sure which one to use . Please advise.
AND datepart(a.trans_dt >="&T1"d) and datepart(a.trans_dt <="&T2"d))

You must not use the inequality as the argument of the DATEPART function. This would mean datepart(1) or datepart(0) (depending on whether the inequality is true or false), both of which are 0, i.e. FALSE (as a Boolean value).

 

AND datepart(a.trans_dt) >="&T1"d and datepart(a.trans_dt) <="&T2"d

The above line would make sense logically, but only if a.trans_dt contains SAS datetime values, which has still not been verified. Not trial and error, but an examination of the unformatted values of variable TRANS_DT should clarify this: Just select TRANS_DT from the database and apply PROC MEANS to this variable.

 

 [Edit: improved wording]

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 14 replies
  • 2181 views
  • 4 likes
  • 4 in conversation