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

reubens — Sat, 04 Sep 2021 23:55:28 GMT

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,

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

Patrick — Sun, 05 Sep 2021 01:04:39 GMT

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;

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

reubens — Sun, 05 Sep 2021 00:31:31 GMT

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

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

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

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

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