Merge two tables using proc sql

[Timbim]

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

[Reeza]

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

---