2

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.

Noza
  • 35
  • 1
  • 7
  • You're doing a `union all` on 1216 tables? What error messages are you getting? Can you provide a [reproducible example](http://www.stackoverflow.com/help/mcve)? – user667489 Jan 24 '17 at 13:01
  • 1216 is the month and year, which identifies each table and it is different for each table. – Noza Jan 24 '17 at 13:15
  • Error: File Table_1 does not exist.Error: File Table_2 does not exist. Error: File Table_3 does not exist. ...... and so on and no union is done finally – Noza Jan 24 '17 at 13:15
  • You are getting that error because you did not test whether the dataset existed! Look at the `%EXIST()` function and the `%IF` macro statement. – Tom Jan 24 '17 at 16:09

5 Answers5

6

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.

data _null_
  • 8,534
  • 12
  • 14
  • 1
    That is super cool. I knew about the : shortcut for variable names, but never thought to try it for dataset names in a SET statement. Thanks for this! – Amw 5G Jan 24 '17 at 15:57
2

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.

Amw 5G
  • 659
  • 5
  • 16
1

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.

user667489
  • 9,501
  • 2
  • 24
  • 35
0

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.);
kstats9pt3
  • 799
  • 2
  • 8
  • 28
  • I like your approach as an alternative to the main approach (data step with wildcard), but it seems like you'd have a much easier time using `dictionary.tables` instead of the proc contents/dedup step, especially since you're already in SQL. – Joe Jan 24 '17 at 16:59
0

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 .

Noza
  • 35
  • 1
  • 7