shorter way to combine rows

[Konkordanz]

Hey forum,

Ive 8 tables with the same columns and only 1 row. I want to combine all the rows into one table. For doing that Im using the following steps...Its working. But: It feels like a dirty method. Is there a better (shorter) way to reach the aim? Thank you!

proc sql;
insert into c5 
select * from c4;
quit;

proc sql;
insert into c6
select * from c5;
quit;

proc sql;
insert into c7 
select * from c6;
quit;

proc sql;
insert into c8 
select * from c7;
quit;

proc sql;
insert into c9
select * from c8;
quit;

proc sql;
insert into c10 
select * from c9;
quit;

proc sql;
insert into c11 
select * from c10;
quit;

[FreelanceReinh]

Hello @Konkordanz,

Try the SET statement with a dataset list in a DATA step:

data want;
set c4-c11;
run;

[Konkordanz]

Thank you! This easy way works! Cause Im learning SAS and SQL im looking also for a solution in SQL...do you have one?

[FreelanceReinh]

---

@Konkordanz wrote:
Thank you! This easy way works! Cause Im learning SAS and SQL im looking also for a solution in SQL...do you have one?

---

If you have the names of the datasets to be combined in a dataset like

data dsnames;
length dsn $3;
do _n_=4 to 11;
  dsn=cat('c',_n_);
  output;
end;
run;

then you can do something like this:

proc sql noprint;
select * into :allds separated by ' union all select * from ' from dsnames;
create table want as select * from &allds;
quit;

However, as KurtBremser said, the DATA step syntax is much shorter.

If no dataset like DSNAMES exists, you can retrieve the dataset names from DICTIONARY.TABLES, but this will require even more PROC SQL code.

[tarheel13]

Use the set operator union in proc sql or concatenation in data step

[Konkordanz]

A solution with sql would be great...but I dont get your point, sry. Can you please show it to me as code?

[ChrisNZ]

SQL is very wordy, so it's not good for the goal here.

SQL would use the UNION operator

Look at the variations on:

proc sql;
  create table HAVE as
  select .. from TABLE1
    <some form of union>
  select .. from TABLE2
    <some form of union>
  select .. from TABLE3;

[Reeza]

Faster run time or faster coding?

For faster coding, you've seen the solutions provided already. For faster run time use PROC APPEND.

proc append base=c4 data=c5; run;
proc append base=c4 data=c6; run;
proc append base=c4 data=c7; run;
....

[Konkordanz]

Faster coding 😉 ... Im leanring SAS and SQL and Im always interested in finding shorter ways for my coding issues.

[Kurt_Bremser]

---

@Konkordanz wrote:
Faster coding 😉 ... Im leanring SAS and SQL and Im always interested in finding shorter ways for my coding issues.

---

Then forget SQL. SQL needs a LOT more code for the UNIONs, the data step is the way to go.