BookmarkSubscribeRSS Feed
Ranjeeta
Pyrite | Level 9

Hello I want to merge the two tables below 

A00 falls in the range A00-A09 therefore I need to add the range A00-A09 and the description column to the table 1 with 3500 rows

 

How would I do that 

 

Table 1 (3,500 rows)

A00 Desc column

A01 Desc

A02 Desc

 

 

Table 2 (293 rows)

A00-A09 Shortname 

6 REPLIES 6
Reeza
Super User
You need to provide more representative data otherwise you're likely going to get an answer that won't scale to your full data set.
Ranjeeta
Pyrite | Level 9
Thankyou its resolved
Reeza
Super User
Please mark the question as solved, by marking the appropriate response or posting the solution that you found.
PeterClemmensen
Tourmaline | Level 20

Does the data have one or two variables here? Please be more specific about what your data looks like

Ranjeeta
Pyrite | Level 9

Thankyou for your response I resolved the same

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Post test data in the form of a datastep please.  As such this is just a guess:

proc sql;
  create table want as
  select a.*,
         b.shortname
  from   table1 a
  left join table2 b
  on     char(a.code,1)=char(b.code,1)
  an     input(substr(scan(b.code,1,"-"),2),best.)<=input(substr(a.code,2),best.)<=input(substr(scan(b.code,2,"-"),2),best.);
quit;

So effectively I am saying where the first character of code match and the numeric parts code is between the numeric part of left hand table2 code and right part of table 2 code.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 626 views
  • 1 like
  • 4 in conversation