BookmarkSubscribeRSS Feed
Sathya3
Obsidian | Level 7

 

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;

 

 

6 REPLIES 6
Sathya3
Obsidian | Level 7

How do I list table names in set statement when value of n is not fixed . n is a sequential number which increments from1 

HenryKobus
Obsidian | Level 7

@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;
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Ksharp
Super User

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;
Sathya3
Obsidian | Level 7

Thanks for your reply. Do you know how to connect to hive tables (hadoop) from SAS and do the same  in HIVE QL

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 914 views
  • 2 likes
  • 4 in conversation