I have a SAS program that uses a DO LOOP to generate about 4,000 lines of code that I then export to a text file. I want to bring that code into pass-through SQL to query tables on Snowflake. How do I do this?
If i were coding entirely in SAS, it would be easy. I would just use %INCLUDE. But apparently that does not work in pass-through SQL.
Here is the relevant code (that doesn't work):
PROC EXPORT DATA=PIMCODE
OUTFILE="/emdata/ADS_Data/BbB/temp/ordspim_code.txt"
DBMS=TAB REPLACE ;
PUTNAMES=NO ;
RUN ;
You didn't include the beginning
EXECUTE
(CREATE OR REPLACE TABLE ADS_DAT.SASUSR.ORDSP AS
SELECT XR.CUST_INDIV_ID
of the code, or the end of the code
FROM XREF_TBL XR
INNER JOIN EDW_PROD.CONSUM_EDS.V_EDS_ORDER_HEADER OH ON XR.CUST_ORDER_NBR = OH.CUST_ORDER_NBR AND XR.CUST_INDIV_ID = OH.CUST_INDIV_ID
GROUP BY XR.CUST_INDIV_ID
) BY SNFLAADS ;
in the text file.
Just fix that.
filename code temp;
data _null_;
file code;
infile
if _n_=1 then put
'EXECUTE BY SNFLAADS'
/ '(CREATE OR REPLACE TABLE ADS_DAT.SASUSR.ORDSP AS'
/ ' SELECT XR.CUST_INDIV_ID'
;
infile "/emdata/ADS_Data/BbB/temp/ordspim_code.txt" end=eof;
input;
put _infile_;
if eof then put
'FROM XREF_TBL XR'
/ ' INNER JOIN EDW_PROD.CONSUM_EDS.V_EDS_ORDER_HEADER OH '
/ ' ON XR.CUST_ORDER_NBR = OH.CUST_ORDER_NBR'
/ ' AND XR.CUST_INDIV_ID = OH.CUST_INDIV_ID'
/ ' GROUP BY XR.CUST_INDIV_ID'
/ ');'
;
run;
Then include this SAS code with the %INCLUDE statement.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.