topic Re: Merging with PROC SQL in SAS Programming

Merging with PROC SQL

BlueNose — Mon, 27 Jul 2015 11:07:00 GMT

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:

ID	Name	Phone	Email
1
2
3

ID	V1	V2	V3
1
2
3

and I wish to create a new dataset:

ID	Name	V1
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 !

Re: Merging with PROC SQL

Ksharp — Mon, 27 Jul 2015 12:08:09 GMT

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;

Re: Merging with PROC SQL

RW9 — Mon, 27 Jul 2015 12:20:43 GMT

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

Re: Merging with PROC SQL

Steelers_In_DC — Mon, 27 Jul 2015 13:06:27 GMT

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;