topic Re: Merging Large Numbers of Dynamic Tables in SAS Programming

Merging Large Numbers of Dynamic Tables

DavidPhillips2 — Mon, 28 Mar 2016 16:32:23 GMT

I’m looking at doing a rather complex data sorting algorithm using temporary tables. Before doing it I’d like to check if this will work. Can I dynamically create a large number of tables e.g. 200 and then merge them together in a set statement dynamically?

I'm looking at using this logic for the set statement

http://support.sas.com/kb/26/010.html

%macro names(prefix,maxnum);
  %do i=1 %to &maxnum;
    &prefix&i
  %end;
  ;
%mend names;

/* Call the macro on the SET statement */

data all;
  set %names(DS,3);
run;

Re: Merging Large Numbers of Dynamic Tables

data_null__ — Mon, 28 Mar 2016 16:41:32 GMT

How about a numbered range or name prefix range.

set DS1-DS3;

set DS:;

Also you might find OPEN=DEFER, SET statement option useful.  If your DSs qualify.

Re: Merging Large Numbers of Dynamic Tables

ballardw — Mon, 28 Mar 2016 16:44:41 GMT

If you are careful with the naming there would be no need for a macro. Since they are temporary then great names like Temp1 to Temp200 suggest themselves. if they are all in the same library then you could use:

data want;

set Temp: ;

run;

The : is a list shortcut that says to use all sets that start with Temp.

Re: Merging Large Numbers of Dynamic Tables

DavidPhillips2 — Mon, 28 Mar 2016 16:46:05 GMT

Thanks data_null_

I'll go with your syntax. Its good to know the huge set won't be an issue.

Re: Merging Large Numbers of Dynamic Tables

data_null__ — Mon, 28 Mar 2016 16:48:12 GMT

It could be that you will run out of memory for data set buffers. That's where open=defer can be useful but only if all the SETS have the exact same variables with the exact same length etc.