topic Re: Inserting code from a text file into pass-through SQL or into a macro in SAS Programming

Inserting code from a text file into pass-through SQL or into a macro

bbenbaruch — Mon, 12 Feb 2024 20:07:28 GMT

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 ;

PROC SQL ; &CONNECTTOADS ;

EXECUTE

(CREATE OR REPLACE TABLE ADS_DAT.SASUSR.ORDSP AS

SELECT

XR.CUST_INDIV_ID

%INCLUDE("/emdata/ADS_Data/BbB/temp/ordspim_code.txt");

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 ;

DISCONNECT FROM SNFLAADS; QUIT;

Re: Inserting code from a text file into pass-through SQL or into a macro

Tom — Tue, 13 Feb 2024 03:38:59 GMT

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.