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 |
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;
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;
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?
I dropped the variable 'case'. It is not needed, but was good for tracking up until that merge.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.