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

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?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

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.

RahulG
Barite | Level 11

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;
Kurt_Bremser
Super User

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.

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
  • 4 replies
  • 940 views
  • 1 like
  • 2 in conversation