topic Re: Join Condition to get the data from both the tables in SAS Programming

Join Condition to get the data from both the tables

David_Billa — Thu, 20 Aug 2020 12:49:25 GMT

I've the datasets as follows.

data have1;
    input Segment $ Expenses RC;
    datalines;
7183_AS1 1234.50 453.97 
7183_AS2 654.78 325.98 
;
run;

data have2;
    input Segment $ Country $ Currency $;
    datalines;
7183_AS1 USA Dollar
7183_AS1 USA Dollar
7183_AS1 USA Dollar
7183_AS2 USA Dollar
7183_AS2 USA Dollar
;
run;

Now I want to get the results as below. I tried with right join and inner join but it didn't worked either.

Segment	Country	Currency Expenses RC
7183_AS1  USA	Dollar	1234.50	453.97
7183_AS1  USA	Dollar	1234.50	453.97
7183_AS1  USA	Dollar	1234.50	453.97
7183_AS2  USA	Dollar	654.78	325.98
7183_AS2  USA	Dollar	654.78	325.98

In summary I want all records from 'have2' plus the matching values from 'Expenses' and 'RC' variables from 'have1'. Any guidance?

Re: Join Condition to get the data from both the tables

PeterClemmensen — Thu, 20 Aug 2020 12:51:36 GMT

Like this?

data want;
   merge have2 have1;
   by Segment;
run;

Re: Join Condition to get the data from both the tables

novinosrin — Thu, 20 Aug 2020 12:53:42 GMT

If SQL,



data have1;
    input Segment $ Expenses RC;
    datalines;
7183_AS1 1234.50 453.97 
7183_AS2 654.78 325.98 
;
run;

data have2;
    input Segment $ Country $ Currency $;
    datalines;
7183_AS1 USA Dollar
7183_AS1 USA Dollar
7183_AS1 USA Dollar
7183_AS2 USA Dollar
7183_AS2 USA Dollar
;
run;

proc sql;
 create table want as
 select a.*,expenses,rc
 from have2 a left join have1 b
 on a.segment=b.segment;
quit;

Re: Join Condition to get the data from both the tables

Kurt_Bremser — Thu, 20 Aug 2020 13:09:16 GMT

What if one of the datasets has no matches? And are the segments unique in have1?

Re: Join Condition to get the data from both the tables

David_Billa — Thu, 20 Aug 2020 13:10:05 GMT

Right, How to do this with join?