CODEWITHSUNDEEP
mergesas
Mateusz Konopelski
Mateusz Konopelski

Reputation: 1042

SAS: Concat columns from different tables (same number of rows)

I have two tables with the same number of rows but not one column I could join them toghether. Like:

data table1(keep=Model) table2(keep=MSRP);
    set sashelp.cars;
run;

How cound I concat table1 and table2 in order to achieve:

enter image description here

In python I would do it as pandas.concat([table1, table2], axis=1) but here anything I try like:

data cancated;
    set table1 table2;
run;

or

proc sql;
    create table joined as
    select * from table1
    union 
    select * from table2;

    delete from joined where Model is missing or MSRP is missing;
run;

but especially the second one gave me error:

ERROR: Column 1 from the first contributor of UNION is not the same type as its counterpart from the second.

So if I udnerstand corectly I cannot have this kind of join with different types of variables.

Thanks!

Upvotes: 0

Views: 1982

Answers (2)

Kiran
Kiran

Reputation: 3315

to do something like in your query. you need to use row number as shown below and then join and delete.

 data table1(keep=Model var) table2(keep=MSRP var);
set sashelp.cars;
var = _n_;
run;

 proc sql;
 create table joined(drop=var) as 
 select a.*, b.* from table1 a
 full join table2 b
 on a.var = b.var;

 delete from joined where Model is missing or MSRP is missing
 ;

Upvotes: 1

Tom
Tom

Reputation: 51566

You can use a merge statement without any by statement to get a row-by-row matching of the observations from two or more datasets.

data concated;
  merge table1 table2;
run;

You could also just use separate set statements for each dataset. 

data concated;
  set table1;
  set table2;
run;

The difference will be when the two datasets have different number of observations. With merge the number of observations will match that of the larger dataset. (The variables contributed only from the smaller dataset will have their values retained.) With the set statements the result will only have the number of observations in the smaller dataset. The step will end when either of the set statements reads past the end of the input dataset.

Upvotes: 2

Related Questions