BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
danwarags
Obsidian | Level 7

Hello Everyone,

 

I have to datasets Dataset A and Dataset B. I want to join age to Table A using variables ID and sales. 

 

   Table A         Table B
IDsales IDsalesage
1car 1car11
1bike  1bike 12
1truck  2car23
2car 2bike 23
3bike  3bike 24
3bike  6bike 32
3truck  4car18
6bike  4truck 19
4car 5bike 20
4truck     
5bike     

 

The output should look like below: 

 

IDsalesage
1car11
1bike 12
1truck .
2car23
3bike 24
3bike 24
3truck .
6bike 32
4car18
4truck 19
5bike 20

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

Now we're talking! See working code below and compare with what you've posted.

data want;
  merge a(in=ina) b;
  by id sales;

  if ina;
run;

proc sql;
  create table want2 as
    select a.*,b.age 
      from a
        left join b
          on a.id=b.id and a.sales= b.sales;
quit;

View solution in original post

5 REPLIES 5
Patrick
Opal | Level 21

What's the problem? Where do you get stuck?

 

Have you tried a SAS data step MERGE with by group {ID, Sales} or a SQL left join with table B on the right side?

danwarags
Obsidian | Level 7

Hello Patrick,

Thanks for quick reply. I tried using both strategies and nothing is working. I want the output as shown above. The merge statement is giiving values from the table B which I wont need. The left join statement I used is giving me different output I expected. Please correct my code if its wrong.

 

data a;
input ID sales$;
datalines;
1	car
1	bike 
1	truck 
2	car
3	bike 
3	bike 
3	truck 
6	bike 
4	car
4	truck 
5	bike 
;
data b;
input ID sales$ age;
datalines;
1	car	    11
1	bike 	12
2	car	    23
2	bike 	23
3	bike 	24
6	bike 	32
4	car	    18
4	truck 	19
5	bike 	20
;
proc sort data= a;
by id sales;
run;
proc sort data= b;
by id sales;
run;

data want;
merge a b;
by id sales;
run;
proc sql;
create table want2 as
select a.*,b.age from a a
left join b b
on a.sales= b.sales;
quit; 

Thanks!

 

Reeza
Super User

Your SQL join condition is incorrect. You need an = for every BY variable (ID and Sales) 

 

Then you should get the correct results. 

Patrick
Opal | Level 21

Now we're talking! See working code below and compare with what you've posted.

data want;
  merge a(in=ina) b;
  by id sales;

  if ina;
run;

proc sql;
  create table want2 as
    select a.*,b.age 
      from a
        left join b
          on a.id=b.id and a.sales= b.sales;
quit;
danwarags
Obsidian | Level 7
Thank you Patrick. This code worked.:-)

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
  • 5 replies
  • 1256 views
  • 3 likes
  • 3 in conversation