Create a Macro to resolve date ranges to use in Proc SQL

[avatar]

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 

[Reeza]

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]

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]

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

thanks  

[avatar]

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]

%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]

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]

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]

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]

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]

---

@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]

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]

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]

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 · Accepted solution]

---

@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]