BookmarkSubscribeRSS Feed
BlueNose
Quartz | Level 8

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 !

3 REPLIES 3
Ksharp
Super User

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Steelers_In_DC
Barite | Level 11

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 747 views
  • 0 likes
  • 4 in conversation