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;
... View more
