Output for 6 month range but no output when extended to 12 months

[Jackie_18]

Hello,

Currently testing a code to pull the end of month data for each month over a 4 year period. Once I have validated my code I will need to pull the data for a few hundred thousand IDs.

The data is being accessed from a historical database which takes a long time to run (this database contains a daily snapshot of all relevant data points for each ID dating back many years). I’m using a pass through query which references imported data containing a sample size of required ID’s to speed up my code.

When I run the data for a six month range, I am able to successfully pull all of the data however when I extend the date range to one year, the output is always null (but the code still runs).

I also keep getting an error in my log “ERROR 180-322: Statement is not valid or it is used out of proper order.” for my 6 month and 12 month code. I’ve validated my 6 month output by pulling the data directly from the historical database and comparing it and it all matches up.

My questions are:

- What could be causing my output to be empty when I extend the date range to one year?
- The 180-322 error - is there anything in my code causing this? Should I be concerned that it might result in missing data if my current output has been validated and the code seems to work fine.

My code is below (fields and table names changed as I cannot post the real names. End date for 12 month range END = '31DEC2020: 0:0: 0 'D;)


Data _NULL_;

START = '01JAN2020:0: 0: 0 ' D;
END = '30JUN2020: 0:0: 0 'D;
format _all_ DDMMYY10.;

ST = PUT (START, DATE9.) ;
ED = PUT (END, DATE9.) ;

CALL SYMPUT ("ST", ST);
CALL SYMPUT ("ED", ED) ;
put (_all_)(/=);
Run;

%LET START_DATE =%SYSFUNC(INTNX(DAY,”&ST.”D, 0, B), DATE9.);

%LET END_DATE =%SYSFUNC(INTNX(DAY,”&ED.”D, 0, B), DATE9.);

%PUT &START_DATE &END_DATE;


PROC SQL;
CONNECT TO ORACLE (connection string") ;

CREATE TABLE SAMPLE_TEST_1 AS

SELECT A.* FROM WORK.SAMPLE E
INNER JOIN
(SELECT * FROM CONNECTION TO ORACLE ( select

B. START_DATE
,B. END_DATE
,B.ID
,C.PRODUCT
,D.ENTRYDATE

FROM DATA.TABLE1 B

LEFT JOIN DATA.TABLE2 C ON B.ID = C.ID AND C.END_DATE=B.END_DATE
LEFT JOIN DATA.TABLE3 D ON B.ID = D.ID AND D.END_DATE=B.END_DATE

WHERE B.END_DATE BETWEEN TO_DATE(%BQUOTE(‘&START_DATE’),’DD-MON-YYYY’) AND TO_DATE(%BQUOTE(&END_DATE’),’DD-MON-YYYY’) ) ) A

ON A.ID = E.ID;

DISCONNECT FROM ORACLE;
QUIT;

[PaigeMiller]

Whenever you have errors in the log, please show us the ENTIRE log for this code. We need to see the ENTIRE log, every single line, every single character, with nothing removed. Please copy the log as text, and paste it into the window that appears when you click on the </> icon.

[Kurt_Bremser]

First, make up your mind if you want to work with dates or datetimes. Date literals use the format

"ddmonyyyy"d

without a time component.

Next, the DAY interval in INTNX with an offset of 0 will simply return the input date value, so the whole function call is useless.

Next, your code contains multiple "curly" quotes, which are not valid, neither in SAS nor in Oracle. I suspect this happens because you edited code at some time in a word processor instead of a text editor, which is a BAD THING. Never do that.

Next, the SAS macro engine will not resolve macro variables inside single quotes.

Finally, Oracle will not recognize dates in DATE9. format. Database engines usually work with dates formatted as YYYY-MM-DD; I have never seen the ddmonyyyy format outside of SAS.

Hint:

First, get your pass-through query to run in the Oracle client. You will then know which code you need to send from SAS. Then, get it to work from within SAS without macro variables. Only then start to make it dynamic.