Read SQL in a iterative process.

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

Read SQL in a iterative process.

Is there any way to read SQL codes from a EXCEl Sheet in an iterative way? I imported the excel sheet as the SAS data and treated each SQL statement as a variable.

I created a macro that to run it

libname db2con db2 username password;

libname tera teradata username password;

libname hari "H:\EDW_to_ICWD";

%macro dev;

DATA _NULL_;

IF 0 THEN SET SASUSER.test2 NOBS=X;

CALL SYMPUT('RECCOUNTICDW', X);

STOP;

RUN;

%DO I=1 %TO &RECCOUNTICDW;

DATA _NULL_;

SET SASUSER.test2(FIRSTOBS=&I);

CALL SYMPUT('VAR1', Query_ID);

CALL SYMPUT('VAR2', SQL1_Query_db2);

cALL SYMPUT('VAR3',SQL2_Query_teradata);

STOP;

RUN;

proc sql ;

create table hari.DB2Table&I as &VAR2;

(select EDW_SQL_Query into :&VAR2  from SASUSER.test2;

quit;

proc sql ;

create table hari.teradataTable&I as &VAR3;

(select ICDW_SQL_Query into :&VAR3  from SASUSER.test2);

quit;

the code worked for the test SQL mimicking temporary library as db2 and teradata but with the connection strings to the databases. It is not running.

syntax error like

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,

              a numeric constant, a datetime constant, a missing value, (, +, -, BTRIM, CALCULATED,

              CASE, EXISTS, INPUT, NOT, PUT, SELECT, SUBSTRING, TRANSLATE, USER, ^,

It is not recognizing the parametrized SQL statement?


Accepted Solutions
Solution
‎01-04-2013 01:40 PM
Super User
Super User
Posts: 6,502

Re: Read SQL in a iterative process.

You could move the PROC SQL, CONNECT and QUIT statements inside the %DO loop.  Then you could put the PROC COMPARE there also.  Or just wrap another %DO loop around the PROC COMPAREs.

%do i=1 %to &nobs;

proc compare base=db2_table&i compare=td_table&i listobs listvar;

run;

%end;


View solution in original post


All Replies
Super User
Super User
Posts: 6,502

Re: Read SQL in a iterative process.

How many observations in your dataset?  Might be easier to just generate the macro variables using SQL.

Is the SQL code designed for SAS or for the source database?  I will assume it is for the source database and that is the source of your syntax errrors.

%macro dev ;

proc sql noprint;

  select Query_ID

       , SQL1_Query_db2

       , SQL2_Query_teradata

    into :id1 - :id9999999

       , :db1 - :db9999999

       , :td1 - :td9999999

    from test2

  ;

%let nobs=&sqlobs ;


  connect to db2 as db2 (user.......) ;

  connect to teradata as td (user.... ) ;

%do i=1 to &nobs ;

   create table hari.db2_table&i as

    select * from connection to db2

    (select &&db&i )

   ;

   create table hari.td_table&i as

     select * from connection to td

     (select &&td&i)

   ;

%end ;


quit;


%mend dev ;


libname hari .... ;

%dev;
Contributor
Posts: 39

Re: Read SQL in a iterative process.

Thanks a million Tom. It worked. The later part of the code. I have to insert the proc compare so I was just curious I should insert the proc compare afer the Quit or just start a new macro for the proc compare.

%macro dev ;

proc sql noprint;

  select Query_ID

       , SQL1_Query_db2

       , SQL2_Query_teradata

    into :id1 - :id9999999

       , :db1 - :db9999999

       , :td1 - :td9999999

    from test2

  ;

%let nobs=&sqlobs ;


  connect to db2 as db2 (user.......) ;

  connect to teradata as td (user.... ) ;

%do i=1 to &nobs ;

   create table hari.db2_table&i as

    select * from connection to db2

    (select &&db&i )

   ;

   create table hari.td_table&i as

     select * from connection to td

     (select &&td&i)

   ;

%end ;


quit;

proc compare base=db2_table&i compare=td_table&i listobs listvar;

run;


%mend dev ;


libname hari .... ;

%dev;
Solution
‎01-04-2013 01:40 PM
Super User
Super User
Posts: 6,502

Re: Read SQL in a iterative process.

You could move the PROC SQL, CONNECT and QUIT statements inside the %DO loop.  Then you could put the PROC COMPARE there also.  Or just wrap another %DO loop around the PROC COMPAREs.

%do i=1 %to &nobs;

proc compare base=db2_table&i compare=td_table&i listobs listvar;

run;

%end;


Contributor
Posts: 39

Re: Read SQL in a iterative process.

  I am able to run those teradata query, but not the db2 query.

I am using OBDC connection for the DB2. Was it due to using the ODBC connection?? I am also using ODBC for the teradata but did not had any issue.

ERROR: CLI prepare error: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token

"END-OF-STATEMENT" was found following "TOPTABLE003228". Expected tokens may include:

"JOIN <joined_table>". SQLSTATE=42601

SQL statement: TOPTABLE003228.

I googled for the connection and it suggested to use explicit pass through, But the program itself is running as a explicit pass-through?

Please help??

Super User
Super User
Posts: 6,502

Re: Read SQL in a iterative process.

Test the code independent of the complex looping to see what is happening.  Your log will be cleaner and you should get clearer error messages. Can you do any queries from DB2?  You probably should open a ticket it SAS support.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 446 views
  • 4 likes
  • 2 in conversation