How to iterate two or three procs in a loop?

Reply
Contributor
Posts: 39

How to iterate two or three procs in a loop?

I have to iterate the some of these procs in a loop.

Right now the process creates two tables each for DB2 and teradata and runs till all the query in the test123 are run and create tables. How could I run  a program that would create both tables, run the proc sort and then proc compare, before iterating it through next Queries of Db2 and Teradata. I want to also delete the table as soon as the data has been compared.

I tried running wrapping % do loops at the end of the procs, meaning wrapping all the procs with one do loop  but it is throwing an error. Previously each procs were wrapped by individual Do loops.

%macro test;

proc sql ;

  select Query_ID

       , DB2_Query

       , TD_QUery

   into :id1 - :id150

      ,:db1 - :db150

      ,:td1 - :td150

         from test123

   ;

   quit;

%let nobs=&sqlobs;

%do i=1 %to &nobs ;

proc sql NOERRORSTOP EXEC;

  connect to db2 as db2 (connection parameter)

create table work.db2_&&ID&I as

    select * from connection to db2

    (&&db&i )

   ;

  

  connect to teradata as td (connection parameter);

execute(&&vt&i) by td;

  create table work.td_&&ID&I as

     select * from connection to td

     (&&td&i)

   ;

  quit;

PROC SORT DATA=chase.db2_&&ID&I

OUT=chase.db2_sort_&&ID&I  ;

  BY _all_ ;

RUN ;

PROC SORT DATA=work.td_&&ID&I OUT=work.td_sort_&&ID&I  ;

  BY _all_ ;

RUN ;

ods listing;

proc compare base=work.db2_sort_&&ID&I compare=work.chase.td_sort_&&ID&I;

run;

ods tagsets.excelxp close;

ods listing close;

%end;

%mend;

%test;

Trusted Advisor
Posts: 1,610

Re: How to iterate two or three procs in a loop?

If its giving you an error, then show us the SASLOG

Super User
Posts: 10,483

Re: How to iterate two or three procs in a loop?

Did you intend to reference CHASE.db2_&&ID&i or work.dbw_&&ID&i since the latter is what was created in the proc sql call?

It looks like you are missing an ODS TAGSETS.EXCELXP file= (etc.) before the proc compare and possibly intended to reverse the order of the ODS listing / listing  close?

Contributor
Posts: 39

Re: How to iterate two or three procs in a loop?

I was trying to add a logic that would delete the datasets results based on the compare results.

If compare results says both of the table are exactly equal then delete the datasets, else keep it.

I am testing 100 of queries in one batch and want to implement the logic that would do it. The code above mention would first make 100 DB2 table, 100 teradata table and sort and make 200 tables. At the end of the test, I would have 400 tables before running to the PROC Compare.

Contributor
Posts: 39

Re: How to iterate two or three procs in a loop?

What my intention is now is to create a process, that would take one DB2 query make a table, one TD Query make a table, sort the table, run the proc compare, publish the result, Based on the result delete the table, if it passed . so I do not run out of space.

Contributor
Posts: 39

Re: How to iterate two or three procs in a loop?

And then move on to the next query. So I want to iterate all the PROCS for each query.

Super User
Posts: 5,255

Re: How to iterate two or three procs in a loop?

As Paige says, what's the error?

As Ballard, I'm also confused about libref reference to Chase.

The is no step for deletion which mention as a requirement. Add a PROC DATASETS at the en of each %do loop, or, don't use macro variable when defining the name for the extract tables, then they will overwrite in each loop.

Data never sleeps
PROC Star
Posts: 1,230

Re: How to iterate two or three procs in a loop?

I'm not sure what your question is any more.  Are you getting an error still, or asking for design help?

If you are trying to figure out how to make a decision based on PROC COMPARE results, it has a return code &sysinfo.

See e.g.

http://support.sas.com/resources/papers/proceedings12/063-2012.pdf

So immediately after the compare you can do something like:

%if &sysinfo=0 %then %do;

  %put Hurrary, the datasets are the same!;

  proc datasets library=work memtype=data;

    delete ... ;

  run; quit;

%end;

%else %do;

  %put Bummer, the datasets are different.

%end;

HTH,

--Q.

SAS Employee
Posts: 5

Re: How to iterate two or three procs in a loop?

Hi DevNand,

I did not really try to understand your code, But this is an (untested) way to loop over some procs.

HTH Jeroen. BTW I have some troubles formatting these messages Smiley Happy. So sorry for that.

proc sql;
create table driver as
select Query_ID
, DB2_Query
, TD_QUery
from test123
;
quit;

%macro do_it(Query_ID, DB2_Query, TD_QUery);

proc sql NOERRORSTOP EXEC;
connect to db2 as db2 (connection parameter)
create table work.db2_&Query_ID as
select * from connection to db2
(&DB2_Query )

;
connect to teradata as td (connection parameter);
execute(<There seems to be missing a teradata query id here>) by td;
create table work.td_&Query_ID as
select * from connection to td
(&TD_QUery)
;
quit;

PROC SORT DATA=chase.db2_&Query_ID

OUT=chase.db2_sort_&Query_ID;
BY _all_;
RUN;

PROC SORT DATA=work.td_&Query_ID OUT=work.td_sort_&Query_ID;
BY _all_;
RUN;

ods listing;

proc compare base=work.db2_sort_&Query_ID compare=work.chase.td_sort_&Query_ID;
run;

/* Delete your files here */

ods tagsets.excelxp close;
ods listing close;

%mend;

data _null_;

set driver;
call execute ('%do_it (' !! Query_ID !! ',' !! DB2_Query !! ',' TD_QUery !! ');');

run;

Ask a Question
Discussion stats
  • 8 replies
  • 591 views
  • 0 likes
  • 6 in conversation