topic How do I merge a variable number of data sets in work in SAS Programming

How do I merge a variable number of data sets in work

eh51 — Tue, 25 Oct 2022 14:59:39 GMT

My SAS program creates 10, more or less, data sets. I would like to merge them with the result of a final want table containing each of the 'results...' variables of each have table. Each dataset has a 'cust' variable that would be the 'by' variable in a data merge setup, or where = in SQL.

My problem is I might expand the number of sets or reduce them.

I can query dictionary and get a nice list of the tables I created. How do I use those in a merge by or sql from?

Two example tables are below.

proc sql ;
  create table tbl_haves as 
  select memname
  from dictionary.tables
  where libname = 'WORK'
    and memname like 'STK%PM' 
  ;
quit;

have_tbl_12ab

case	cust	results12ab
11	1271	-2217385
10	1279	-2231597
9	1292	-2254441
8	1305	-2276959
7	1318	-2299131
6	1331	-2320933
5	1344	-2342338
4	1357	-2363316
3	1370	-2383831
2	1383	-2403842
1	1396	-2423304

have_tbl_45cv

case	cust	results45cv
154	1271	-2291000
153	1279	-2317000
152	1292	-2341000
151	1305	-2364000
150	1318	-2388000
149	1331	-2413000
148	1344	-2438000
147	1357	-2460000
146	1370	-2484000
145	1383	-2508000
144	1396	-2530000

Re: How do I merge a variable number of data sets in work

Tom — Tue, 25 Oct 2022 17:31:00 GMT

To merge multiple dataset just use the MERGE statement.

data want;
  merge ds1 ds2 ds3 ;
  by customer;
run;

If you put the list of dataset names into a macro variable you can use that to generate the code.

%let dslist=ds1 ds2 ds3 ;
data want;
  merge &dslist;
  by customer;
run;

You can change your PROC SQL query to generate a macro variable instead of a dataset.

proc sql noprint;
select nliteral(memname) into :dslist separated by ' ' 
  from dictionary.tables
  where libname = 'WORK'
    and memname like 'STK%PM' 
;
quit;

Re: How do I merge a variable number of data sets in work

Astounding — Tue, 25 Oct 2022 15:11:20 GMT

What do you intend to do with the variable CASE, which appears in all data sets, but varies from one data set to the next?

Re: How do I merge a variable number of data sets in work

eh51 — Tue, 25 Oct 2022 17:20:10 GMT

I dropped the variable 'case'. It is not needed, but was good for tracking up until that merge.