Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

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

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 


Accepted Solutions
Solution
‎04-28-2016 10:39 AM
Trusted Advisor
Posts: 1,114

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

[ Edited ]

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


All Replies
Grand Advisor
Posts: 17,332

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

 

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));
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

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

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;
Contributor
Posts: 66

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

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

 

thanks  

Contributor
Posts: 66

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

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;
Contributor
Posts: 66

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

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

 

 

Grand Advisor
Posts: 17,332

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

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)
Contributor
Posts: 66

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

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.

Grand Advisor
Posts: 17,332

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

[ Edited ]

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.

Contributor
Posts: 66

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

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
Trusted Advisor
Posts: 1,114

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


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

Contributor
Posts: 66

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

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
Trusted Advisor
Posts: 1,114

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

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.

Contributor
Posts: 66

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

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;
Solution
‎04-28-2016 10:39 AM
Trusted Advisor
Posts: 1,114

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

[ Edited ]

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]

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 442 views
  • 4 likes
  • 4 in conversation