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

[bbenbaruch]

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;

[Tom]

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.