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;
If its giving you an error, then show us the SASLOG
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?
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.
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.
And then move on to the next query. So I want to iterate all the PROCS for each query.
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.
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.
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 . 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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.