I am trying to create a macro that takes a base table, loops through a library of tables and adds (in this specific case) one field from each append table to the base table (for example, take a list of baseball players and loop through a library of monthly stats, adding one column from each monthly table to the base table).
I am running into an issue where the last step (creating the 'Combine' table fails because table c1 is not 'properly sorted.' What confuses me is that the way this is written, it is using the same base table through each pass through the loop, so there should be no changes to sorting, nor missing/additional records in each pass. Any help is appreciated, especially if there is a more efficient way to accomplish this goal. Thanks!
/*Create base data set*/
Data r;
set r.all;
run;
/*Get list of column names from base data set*/
proc sql noprint;
select name
into: base separated by " "
from dictionary.columns
where memname = 'r';
quit;
%put &base.;
/*Get a list of all tables in the append library*/
proc contents data =a._all_ noprint out=ds_names(keep=memname); run;
/*Get a distinct list of just the tables you want to include*/
proc sql noprint;
select distinct(memname)
into: ds_list separated by " "
from ds_names
where substr(memname,1,6)='AT' and 201701 <= input(substr(memname,7,6),best6.) <= 201806;
quit;
%put &ds_list.;
%macro shoop(ds_list);
%do i=1 %to 2;
proc sql;
create table c&i. as
select a.*, b.status as %scan(&ds_list, &i)
from r as a
left join a.%scan(&ds_list, &i) as b on (a.id = b.id)
order by &base.;
run;
%end;
data combine;
merge
%do i=1 %to 2;
c&i
%end;;
by &base.;
run;
%mend;
%shoop(&ds_list.);
... View more
