CODEWITHSUNDEEP
loopssasunionproc-sql
Noza
Noza

Reputation: 35

Looping through tables and joining them in SAS

I can't figure out to solve one problem.

I have unknown number of tables in one SAS library. I want to make a loop, which goes through them and "UNION ALL" those tables.

Tables are pre-checked and they have the same structure. This tables include historicized data, so therefore it should be Union all. I tried following script, but it searches for all possibilities but does not do UNION ALL.

I appreciate any ideas to solve this.

%macro union_tables(table_name, last_extract); 
 %do i=1 %to &last_extract.;
        select * from data.&table_name&i
        union all
 %end;
        select * from data.&table_name&i
%mend;

proc sql; 
create table Full_history as 
%union_tables(Table_,1216);
quit;

Thank you in advance.

Upvotes: 2

Views: 3181

Answers (5)

Noza
Noza

Reputation: 35

Thank you all for your responses. I used little of all to achieve the result I needed.

I want to post my own result because I used partially codes from Amw 5G and Foxer users. 

/* get list of the tables */ 

%MACRO SHOW_TABLES(libname, regex);
proc sql noprint;
select memname
into :list_tables separated by " "

from dictionary.tables
where libname="&libname." and upcase(memname) like upcase("%quote(%%)&&regex%")
order by memname;
quit;

%MEND;

%show_tables(DATA, Table_);

%put LIST OF TABLES: &list_tables.;

/* putting data sets together*/
DATA data.Full_history;
SET data.&list_tables.;
RUN;

This way it puts together all my tables even if there is a different structure. Because tables can change over the time (some columns can be deleted or added new ones).

For the proc append table should have the same structure. Therefore, in my case its reasonable to use data set .

Upvotes: 0

kstats9pt3
kstats9pt3

Reputation: 873

No matter how your datasets are named, just put them all into a list after creating an order variable and set them together (replace work. with whatever your library is named):

/* put all dataset names into a dataset */
proc contents data = work._all_ noprint out=ds_names(keep=memname); run;

/* dedupe the dataset and create an order variable */
data ds_names1; set ds_names;
    by memname;
    if first.memname;
    order_var + 1;
run;

/* put the ordered datasets into a macro variable list */
proc sql noprint;
    select distinct(memname)
    into: ds_list separated by " "
    from ds_names1
    order by order_var;
quit;

%put &ds_list.;

/* append all the datasets together in order */
%macro append_instead_of_set(ds_list);
%do i=1 %to %sysfunc(countw(&ds_list.));
    %let ds = %scan(&ds_list.,&i.);
    proc append data = &ds. base=full force; run;
%end;
%mend;

%append_instead_of_set(&ds_list.);

Upvotes: 0

data _null_
data _null_

Reputation: 9109

Perhaps you could dispense with macro and use the SET statement.

data Many;
   set table_: open=defer;
   run;

If the tables are exactly the same with regards to variables and type Open=defer can save a lot of memory.

Upvotes: 6

Amw 5G
Amw 5G

Reputation: 689

If: A) the total length of all your filenames + their libname prefix + period is < 32767; and B) your constituent files are all in one library; and C) your files all begin with 'Table_'

data _null_;
length datasets $32767; *make sure this is long enough to hold all your dataset names + their libname prefix.  32767 is the max string length;
retain datasets;
set sashelp.vstable end=eof; *This system view holds all known datasets in all known libraries;
where upcase(libname)='DATA'; *The libname where you dsets live.  Use all uppercase;
if index(UPCASE(memname),'TABLE_') =1 /*Your code shows all datasets begin with 'Table_'*/ then
datasets=trim(datasets)||
 ' '||trim(libname)||
 '.'||trim(memname);
if eof then call symput('datasets',datasets);
run; 
%put &datasets; *print them to the log;

data all_the_datasets;
set &datasets;
run;

For reference, I cribbed much of this from p5 of a SUGI 29 paper by Richardson & Rossland.

Upvotes: 2

user667489
user667489

Reputation: 9569

Your macro is looking for tables named table_1 through to table_1216 and giving you an error when it can't find the ones that don't exist. You need to change your loop so that it only attempts to take the union of tables that actually exist.

Upvotes: 1

Related Questions