03-27-2013 04:09 PM
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.
proc sql ;
into :id1 - :id150
,:db1 - :db150
,:td1 - :td150
%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
connect to teradata as td (connection parameter);
execute(&&vt&i) by td;
create table work.td_&&ID&I as
select * from connection to td
PROC SORT DATA=chase.db2_&&ID&I
BY _all_ ;
PROC SORT DATA=work.td_&&ID&I OUT=work.td_sort_&&ID&I ;
BY _all_ ;
proc compare base=work.db2_sort_&&ID&I compare=work.chase.td_sort_&&ID&I;
ods tagsets.excelxp close;
ods listing close;
03-27-2013 05:51 PM
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?
03-27-2013 06:12 PM
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.
03-27-2013 06:15 PM
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.
03-28-2013 03:59 AM
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.
03-28-2013 05:11 AM
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.
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 ... ;
%put Bummer, the datasets are different.
03-28-2013 09:41 AM
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 . So sorry for that.
|create table driver as|
%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|
|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|
|PROC SORT DATA=chase.db2_&Query_ID|
|PROC SORT DATA=work.td_&Query_ID OUT=work.td_sort_&Query_ID;|
|proc compare base=work.db2_sort_&Query_ID compare=work.chase.td_sort_&Query_ID;|
|/* Delete your files here */|
|ods tagsets.excelxp close;|
|ods listing close;|
|call execute ('%do_it (' !! Query_ID !! ',' !! DB2_Query !! ',' TD_QUery !! ');');|