BookmarkSubscribeRSS Feed
devnand
Obsidian | Level 7

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;

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
ballardw
Super User

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?

devnand
Obsidian | Level 7

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.

devnand
Obsidian | Level 7

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.

devnand
Obsidian | Level 7

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

LinusH
Tourmaline | Level 20

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
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Mike Raithel presenting on validating data files on Wednesday July 17. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
JeroenJeurissen
SAS Employee

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1256 views
  • 0 likes
  • 6 in conversation