BookmarkSubscribeRSS Feed
bbenbaruch
Quartz | Level 8

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;
1 REPLY 1
Tom
Super User Tom
Super User

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.

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 415 views
  • 2 likes
  • 2 in conversation