topic Re: Create a Macro to resolve date ranges to use in Proc SQL in SAS Enterprise Guide

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

avatar — Wed, 27 Apr 2016 14:46:14 GMT

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

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

Reeza — Wed, 27 Apr 2016 14:50:44 GMT

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

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

RW9 — Wed, 27 Apr 2016 15:26:59 GMT

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;

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

avatar — Wed, 27 Apr 2016 15:41:59 GMT

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

thanks

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

avatar — Wed, 27 Apr 2016 15:48:40 GMT

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;

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

avatar — Wed, 27 Apr 2016 16:22:36 GMT

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

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

Reeza — Wed, 27 Apr 2016 16:55:53 GMT

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)

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

avatar — Wed, 27 Apr 2016 17:09:40 GMT

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.

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

Reeza — Wed, 27 Apr 2016 17:19:51 GMT

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.

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

avatar — Wed, 27 Apr 2016 17:24:40 GMT

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

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

FreelanceReinh — Wed, 27 Apr 2016 21:25:10 GMT

@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."

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

avatar — Thu, 28 Apr 2016 13:02:40 GMT

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

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

FreelanceReinh — Thu, 28 Apr 2016 13:33:23 GMT

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.

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

avatar — Thu, 28 Apr 2016 13:49:40 GMT

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;

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

FreelanceReinh — Thu, 28 Apr 2016 15:30:24 GMT

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