How Do I Use a Macro to filter on Yesterday's date using a PROC SQL ODBC connection

Reply
New Contributor
Posts: 2

How Do I Use a Macro to filter on Yesterday's date using a PROC SQL ODBC connection

Hello,

 

I'm writing code in EG 9.4 and I'm trying to use a function to filter a table using yesterday's date and am having a difficult time.  I have tried using any combination of %sysfunc %eval Today() in a macro variable but for some reason its not working.  I think it may be because I'm filtering in the ODBC connection, but the table I'm querying from is very large and it would save a ton of query time if I filtered there.

 

Some macros that I've tried using (of course all not run on the same run):

%let TEST= %sysfunc(%eval(Today()-1),DATE9.);
%let TEST= %eval(TODAY()-1);
%let TEST= %put %sysfunc(putn(%eval(%sysfunc(today())-1),date9.));
%let TEST= %put %sysfunc(intnx(day,%sysfunc(today()),-1),date9.);
%let TEST= %put %eval(%sysfunc(today())-1);
%let TEST = %eval(%sysfunc(today(),)-1);

 

CONDENSED VERSION OF MY CODE:

Proc SQL;
RESET;
Connect to odbc (authdomain=db2_auth DATAsrc=db2dsne);
CREATE TABLE WORK.TABLE AS
SELECT
NAME,
RPT_PRD_BG_DT


FROM CONNECTION to odbc(

SELECT
PDT_SERV_NME AS NAME,
RPT_PRD_BG_DT

FROM
XDPW0000.XVPW0100_TABLE_NAME
WHERE RPT_PRD_BG_DT = &TEST.

);

 

Any help is appreciated!

 

Thanks!

Esteemed Advisor
Posts: 6,692

Re: How Do I Use a Macro to filter on Yesterday's date using a PROC SQL ODBC connection

See my answer in your other thread concerning yesterday's date.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 1 reply
  • 239 views
  • 0 likes
  • 2 in conversation