CODEWITHSUNDEEP
sasproc-sqldo-loops
B K
B K

Reputation: 59

SAS Loop through a table of datasets to compare

I have two folders (librefs) containing numerous datasets. Each folder contains identical datasets and I'd like to compare SPECIFIC datasets (not all) within each folder to ensure they are similar. Is it possible to loop through each folder and compare each dataset?

proc sql;
   create table specific_tables (table_name CHAR(100));
     insert into all specific_tables
     values(name_of_dataset1)
     values(name_of_dataset2)
     values(name_of_dataset3)
     values(name_of_dataset4)
     values(name_of_dataset5)
;
quit;

Both folders contain these datasets. How can I loop through this table of datasets and compare them from each folder?

%macro compare;

libname a "file path";
libname b "file path";

%do i %to number of datasets to compare;

proc compare base = a.name_of_dataset&i
compare= b.name_of_data&I
run;

 %end;
%mend;

Upvotes: 0

Views: 178

Answers (1)

Reeza
Reeza

Reputation: 21294

I would recommend a slightly different structure.

  • Assign library
  • Create macro to do comparison, with the parameter being the data set name
  • Call the macro once for each table in the list of tables
*assign libraries;
libname a "file path";
libname b "file path";

*define macros for comparison;
%macro compare(dsn=);

*run comparison;
proc compare base = a.&dsn
compare= b.&dsn;
run;

*store results in macro variable;
%let rc = &sysinfo.;

*save results into dataset;
data _temp;
length dsn $32. results 8.;
dsn = "&dsn.";
results = ifn(&rc=0, 0, 1);
run;

*append to data set to keep over iterations;
proc append base=comparison_results data=_temp;
run;

*clean up between runs to prevent issues;
proc sql;
drop table _temp;
quit;

%symdel rc;

%mend;


*call macro once for each dataset in list;
data execute;
set specific_tables;

str = catt('%compare(dsn=', table_name, ');');

call execute(str);

run;

Upvotes: 2

Related Questions