Hi ,
I have a requirement where I have to combine tables using union all.
List of source tables are like Table_1, Table_2...so on till table_n where n is not a fixed number and
it is a sequential number from 1 upwards
My problem here is How to identify the final value of n (since it changes from time to time)
and vertically combine datasets with n suffixed to them
Below is sample code :
data table_1;
aaa
bbb
run;
data table_2;
aa
bb
run;
data table_3;
a
b
c
run;
Proc sq1;
create table final as
select * from table_1
union all
select * from table_2
union all
select * from table_3
union all;
quit;
How do I list table names in set statement when value of n is not fixed . n is a sequential number which increments from1
@Ksharp showed the answer of your question already : ":" works as a wildcard so all tables starting with "table_" will be used to create the result. I made your example runable so you may test it:
data table_1;
length value $10.;
input value;
datalines;
aaa
bbb
;
run;
data table_2;
length value $10.;
input value;
datalines;
aa
bb
;
run;
data table_3;
length value $10.;
input value;
datalines;
a
b
c
;
run;
data final ;
set table_: ;
run;
You can also do it the "lazy way" using the noDSMFERR option:
data table_1 table_2 table_3 table_7;
length value $10.;
input value;
datalines;
aaa
bbb
;
run;
data table_22 table_11;
length value $10.;
input value;
datalines;
aa
bb
;
run;
data table_112 table_897;
length value $10.;
input value;
datalines;
a
b
c
;
run;
option noDSNFERR;
data final ;
set table_1-table_9 table_10-table_99 table_100-table_999 /* ... add more if needed */ indsname=in;
indsname=in; /* <- this variable keeps data set name */
run;
option DSNFERR;
All the best
Bart
You can sort the table name by DICTIONARY table.
data table_1;
length value $10.;
input value;
datalines;
aaa
bbb
;
run;
data table_2;
length value $10.;
input value;
datalines;
aa
bb
;
run;
data table_3;
length value $10.;
input value;
datalines;
aaa
bbb
;
run;
data table_22;
length value $10.;
input value;
datalines;
aa
bb
;
run;
data table_11;
length value $10.;
input value;
datalines;
a
b
c
;
run;
proc sql noprint;
select memname into :list separated by ' '
from dictionary.tables
where libname='WORK' and memname like 'TABLE%' /*<--- must be capital */
order by input(compress(memname,,'kd'),best.);
quit;
data final ;
set &list. ;
run;
Thanks for your reply. Do you know how to connect to hive tables (hadoop) from SAS and do the same in HIVE QL
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.