Merging multiple datasets in SAS using a MACRO - Merge the last dataset to the last MERGED dataset

[reubens]

Hi there:

RE: Merging multiple datasets in SAS using a MACRO - Merge the last dataset to the last MERGED dataset

Note:

- I'm not stacking the data (so proc append is not required)

- Is there a more eloquent way of writing my SAS code below (The SAS code works but I'm sure there is a simpler way to write a SAS program in order to merge the last dataset to the last MERGED dataset). Any help would be greatly appreciated

I'm using a macro, where I have a main_dataset (called A) and other_datasets to merge (called B, C, D, E, F)

The below SAS code that I've worked out (after reading all these forums) is below. 

However, I've used the macro below to ensure that when I perform a multiple merge that I "merge the last dataset to the last MERGED dataset"

run %XXX(A,B); .... to get merged
run %XXX(merged,C); .... and then the main dataset always becomes merged
run %XXX(merged,D);
run %XXX(merged,E);
run %XXX(merged,F);
run %XXX(merged,G);

Is there a more eloquent way of doing the code than what's written above & below

The SAS code is below

/* +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */
/* +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */
%macro XXX(main_dataset,other_dataset);
/* +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */
/* +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */

proc sort data=N.&main_dataset;
by DSCD year month_name;
run;

proc sort data=N.&other_dataset.;
by DSCD year month_name;
run;

data N.merged;
merge N.&main_dataset (in=indata)
N.&other_dataset (in=inb);

by DSCD year month_name;
if indata;
* if indata and inb;

where DSCD in ('992816');
run;

/* +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */
%mend;
/* +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */

%XXX(A,B);
%XXX(merged,C);
%XXX(merged,D);
%XXX(merged,E);
%XXX(merged,F);
%XXX(merged,G);

/* Note: I place merged after the ( ...... in order to merge the last dataset to the last MERGED dataset. Is there a better way to do this?

%XXX(merged,C);
%XXX(merged,D);
%XXX(merged,E);
%XXX(merged,F);
%XXX(merged,G);

Thanks,

R

[Patrick · Accepted solution]

Merging all the tables at once will be more efficient as you don't have to pass through the data multiple times. 

Applying the data selection prior to sorting will also be more efficient because then you only need to sort the selected rows and not the full table.

%macro prep_tbl(inds,outview);
  proc sql;
    create view &outview as
    select *
    from &inds
    where DSCD ='992816'
    order by DSCD, year, month_name
    ;
  quit;
%mend;
%prep_tbl(a,v_1);
%prep_tbl(b,v_2);
%prep_tbl(c,v_3);
%prep_tbl(d,v_4);
%prep_tbl(e,v_5);
%prep_tbl(f,v_6);

data want;
  merge v_1 (in=v1) v_2 - v_6;
  by DSCD year month_name;
  if v1;
run;

[reubens]

That's an amazing program. Thanks very much Patrick for sharing and providing such a speedy response