topic Re: Vertiacally combine dataset based on sequential number which increments from 1 upwards. in SAS Programming

Vertiacally combine dataset based on sequential number which increments from 1 upwards.

Sathya3 — Wed, 21 Jun 2023 05:30:17 GMT

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;

Re: Vertiacally combine dataset based on sequential number which increments from 1 upwards.

Ksharp — Wed, 21 Jun 2023 05:36:59 GMT

Why not :

data final ;
set table_: ;
run;

Re: Vertiacally combine dataset based on sequential number which increments from 1 upwards.

Sathya3 — Wed, 21 Jun 2023 05:56:02 GMT

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

Re: Vertiacally combine dataset based on sequential number which increments from 1 upwards.

HenryKobus — Wed, 21 Jun 2023 06:44:47 GMT

@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;

Re: Vertiacally combine dataset based on sequential number which increments from 1 upwards.

Ksharp — Wed, 21 Jun 2023 08:37:27 GMT

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;

Re: Vertiacally combine dataset based on sequential number which increments from 1 upwards.

Sathya3 — Thu, 22 Jun 2023 06:34:34 GMT

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

Re: Vertiacally combine dataset based on sequential number which increments from 1 upwards.

yabwon — Thu, 22 Jun 2023 07:09:54 GMT

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