topic Macro Loop to Append Columns to a Base Table from a every table in a Library in SAS Programming

Macro Loop to Append Columns to a Base Table from a every table in a Library

P5C768 — Tue, 07 Aug 2018 22:33:40 GMT

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.);

Re: Macro Loop to Append Columns to a Base Table from a every table in a Library

ballardw — Tue, 07 Aug 2018 23:11:19 GMT

@P5C768 wrote:

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).

You would be much better off Appending data sets as long as those monthly data sets have a date or variable that indicates which month is involved.

Then the code for manipulating any of those stats gets much easier. It looks like:

data want;

set at201701 - at201806;

run;

would work. With use of the INDSNAME option on the set statement you could even build a date value from the apparent data set names.

The report procedures such as Proc Tabulate or Report would build output tables using that date value to make a column per month, quarter, year, or even season with a small amount of work.

Re: Macro Loop to Append Columns to a Base Table from a every table in a Library

novinosrin — Wed, 08 Aug 2018 00:16:03 GMT

@ballardw Sir, hats off to your healthy eyes and super fast catching spirit while I am so tired by the evening. I like your neat approach. 🙂