topic Re: join two tables in SAS Programming

join two tables

RahulG — Wed, 25 Oct 2017 05:57:03 GMT

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?

Re: join two tables

Kurt_Bremser — Wed, 25 Oct 2017 06:26:41 GMT

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

Re: join two tables

Kurt_Bremser — Wed, 25 Oct 2017 06:34:07 GMT

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.

Re: join two tables

RahulG — Wed, 25 Oct 2017 07:57:29 GMT

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;

Re: join two tables

Kurt_Bremser — Wed, 25 Oct 2017 08:18:58 GMT

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.