DATA Step, Macro, Functions and more

Merging with PROC SQL

Reply
Regular Contributor
Posts: 180

Merging with PROC SQL

Hello

I am trying to merge two data sets using PROC SQL and getting error messages.

For illustration, let's say I have these two data sets:

A:

IDNamePhoneEmail
1
2
3

B:

IDV1V2V3
1
2
3

and I wish to create a new dataset:

IDNameV1
1
2
3

using PROC SQL. I tried it, it didn't work. It did work when I took all the variables, using *, but when I wanted to choose, it failed. I want just V1 from the second table, and ID and Name from the first, where the matching is by ID. I don't want to use * and then drop variables, because this is long, and I can do that with a "normal" data step. PROC SQL should make life easier, I think.

Thank you !

Super User
Posts: 9,673

Re: Merging with PROC SQL

It is very weird  that SQL couldn't work.

proc sql;

create table want as

select a.id,a.name,b.v1

   from a,b

    where a.id=b.id ;

quit;

Super User
Super User
Posts: 7,399

Re: Merging with PROC SQL

You would need to post your code, and partial log. 

Valued Guide
Posts: 858

Re: Merging with PROC SQL

Without your code or log I can only speculate to what the error is, make sure you are naming the tables and using the reference from that table to identify what variables you want.  Notice here I name the table 'a' and 'b' and use the corresponding letters to identify the source for the variables I'm keeping.

data have1;

infile cards dsd;

input id name phone email;

cards;

1,,,

2,,,

3,,,

;

data have2;

infile cards dsd;

input id v1 v2 v3;

cards;

1,,,

2,,,

3,,,

;

proc sql;

create table want as

select a.id,a.name,b.v1

from have1 a inner join     

     have2 b on

a.id = b.id;

Ask a Question
Discussion stats
  • 3 replies
  • 202 views
  • 0 likes
  • 4 in conversation