topic Merge tables in New SAS User

Merge tables

Ranjeeta — Thu, 15 Nov 2018 16:08:43 GMT

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

Re: Merge tables

Reeza — Thu, 15 Nov 2018 16:12:24 GMT

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.

Re: Merge tables

PeterClemmensen — Thu, 15 Nov 2018 16:12:26 GMT

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

Re: Merge tables

RW9 — Thu, 15 Nov 2018 16:13:37 GMT

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.

Re: Merge tables

Ranjeeta — Fri, 23 Nov 2018 19:43:50 GMT

Thankyou for your response I resolved the same

Re: Merge tables

Ranjeeta — Fri, 23 Nov 2018 19:44:18 GMT

Thankyou its resolved

Re: Merge tables

Reeza — Fri, 23 Nov 2018 21:41:21 GMT

Please mark the question as solved, by marking the appropriate response or posting the solution that you found.