DATA Step, Macro, Functions and more

join two tables

Accepted Solution Solved
Reply
Super Contributor
Posts: 271
Accepted Solution

join two tables

I have two tables 

table 1 has Primary Key column  whose value are

10000_John

10001_Andrew

10002_Sam

 

table2 has Primary Key whose value are

10000

10001

10002

 

I have to join both the tables based on Primary key values

Can I join them directly without manipulating any value in table 1?

 

 


Accepted Solutions
Solution
‎10-25-2017 04:26 AM
Super User
Posts: 10,623

Re: join two tables

[ Edited ]

Note that in my example, the shorter key variable was defined according to the length of its values. No sense in wasting space.

If you have badly defined key variables (something to avoid, for obvious reasons), use the trim() function:

proc sql;
create table want as select a.id, a.age,b.marks1, b.marks2
from have1 a, have2 b
where index(a.id,trim(b.id)) = 1
;
quit;

Keep in mind that comparison functions like index() or find() will take the blanks with which overlong variables are padded into account.

See Maxim 46.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 10,623

Re: join two tables

Please post your example data as data steps, so we can recreate the data with copy/paste and submit.

Use the proper subwindow for the code, see https://communities.sas.com/t5/help/faqpage/faq-category-id/posting

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,623

Re: join two tables

You can do the join in SQL:

proc sql;
create table want as select a.*
from have1 a, have2 b
where index(a.primkey,b.primkey) = 1
;
quit;

For a data step merge, you have to create a new variable in have1 that fits the key of have2.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 271

Re: join two tables

Posted in reply to KurtBremser

I tried it but its not working

data Have1;
length ID $20.;
input ID $ age;
datalines;
10000_John 25
10001_Andrew 24
10002_Sam 22
;

data have2;
length ID $20.;
input ID $ marks1 marks2;
datalines;
10000 75 78
10001 58 89
10002 85 73
;

proc sql;
create table want as select a.id, a.age,b.marks1, b.marks2
from have1 a, have2 b
where index(a.id,b.id) = 1
;
quit;
Solution
‎10-25-2017 04:26 AM
Super User
Posts: 10,623

Re: join two tables

[ Edited ]

Note that in my example, the shorter key variable was defined according to the length of its values. No sense in wasting space.

If you have badly defined key variables (something to avoid, for obvious reasons), use the trim() function:

proc sql;
create table want as select a.id, a.age,b.marks1, b.marks2
from have1 a, have2 b
where index(a.id,trim(b.id)) = 1
;
quit;

Keep in mind that comparison functions like index() or find() will take the blanks with which overlong variables are padded into account.

See Maxim 46.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 134 views
  • 1 like
  • 2 in conversation