Merge two tables using proc sql

Timbim — Fri, 29 Jun 2018 00:28:27 GMT

Hi there,

I have two tables to merge and whilst I know how to merge using both SAS data step and Proc Sql, I prefer to use proc sql however the problem I am facing is how to keep all columns for the first table and only specific columns for the second table.

It should be

Proc Sql;

Create table Accounts as

Select * from table 1

Select a, b,c from table 2

left join table 2 on ( table1.a=table2.a);

quit;

I truly appreciate your help.

Kind regards,

Mags

Re: Merge two tables using proc sql

Reeza — Fri, 29 Jun 2018 02:00:52 GMT

Please post your code using a code box. In the Rich Text Editor it's the 6/7th icons (notebook or i). This makes it easier to copy/paste, doesn't introduce emoticons and its more legible.

You use * as a wildcard to reference all columns. To reference all columns in a specific table list it as either 'TableName.*' or 'Alias.*'.

Typically I use a table alias so I don't have to type out the full name. t1/t2/tN is common. You can also use this convention on the join conditions and elsewhere in the query. See example below:

Proc Sql;

Create table Accounts as
Select t1.*, t2.ID, t2.Age
from table1 as t1 /*alias is t1 for table1*/
left join  table2 as t2 /*alias is t2 for table2*/
on t1.a=t2.a;

quit;

@Timbim wrote:

Hi there,

I have two tables to merge and whilst I know how to merge using both SAS data step and Proc Sql, I prefer to use proc sql however the problem I am facing is how to keep all columns for the first table and only specific columns for the second table.

It should be

Proc Sql;

Create table Accounts as

Select * from table 1

Select a, b,c from table 2

left join table 2 on ( table1.a=table2.a);

quit;

I truly appreciate your help.

Kind regards,

Mags

topic Merge two tables using proc sql in SAS Programming

Merge two tables using proc sql

Re: Merge two tables using proc sql