topic Re: shorter way to combine rows in SAS Programming

shorter way to combine rows

Konkordanz — Tue, 14 Sep 2021 09:36:54 GMT

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;

Re: shorter way to combine rows

FreelanceReinh — Tue, 14 Sep 2021 10:27:52 GMT

Hello @Konkordanz,

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

data want;
set c4-c11;
run;

Re: shorter way to combine rows

tarheel13 — Tue, 14 Sep 2021 12:52:00 GMT

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

Re: shorter way to combine rows

Reeza — Tue, 14 Sep 2021 15:28:42 GMT

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

Re: shorter way to combine rows

Konkordanz — Wed, 15 Sep 2021 06:31:39 GMT

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

Re: shorter way to combine rows

Konkordanz — Wed, 15 Sep 2021 06:33:01 GMT

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

Re: shorter way to combine rows

Konkordanz — Wed, 15 Sep 2021 06:34:06 GMT

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

Re: shorter way to combine rows

Kurt_Bremser — Wed, 15 Sep 2021 06:36:49 GMT

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

Re: shorter way to combine rows

FreelanceReinh — Wed, 15 Sep 2021 08:04:57 GMT

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

Re: shorter way to combine rows

ChrisNZ — Wed, 15 Sep 2021 09:00:29 GMT

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;