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:
ID | Name | Phone | |
---|---|---|---|
1 | |||
2 | |||
3 |
B:
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 !
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;
You would need to post your code, and partial log.
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.