DATA Step, Macro, Functions and more

Merge two tables using proc sql

Reply
Contributor
Posts: 30

Merge two tables using proc sql

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

Super User
Posts: 23,683

Re: Merge two tables using proc sql

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


 

Ask a Question
Discussion stats
  • 1 reply
  • 49 views
  • 0 likes
  • 2 in conversation