Text Field inside Ranges

[angeliquec]

I have a REF_TABLE containing some ranges.

If street number from the HAVE table field (address field in text) falls under the ranges in the REF_TABLE, how can I classify the address field based on the range group in the REF_TABLE?

Here are the codes for the tables:

1. REF_TABLE

data REF_TABLE;

infile datalines dlm="|";

input STARTNUM ENDNUM GROUP :$10.;

datalines;

0|10|GROUP1

11|20|GROUP2

;

run;

2. HAVE table

data HAVE;

infile datalines dlm="|";

input ADDRESS :$20.;

datalines;

1 STREET

#11 STREET

;

run;

3. WANT table (expected result)

data WANT;

infile datalines dlm="|";

input ADDRESS :$20. GROUP :$10.;

datalines;

1 STREET|GROUP1

#11 STREET|GROUP2

;

run;

Thank you!

[novinosrin · Accepted solution]

data REF_TABLE;

infile datalines dlm="|";

input STARTNUM ENDNUM GROUP :$10.;

datalines;
0|10|GROUP1
11|20|GROUP2
;
run;

data HAVE;

infile datalines dlm="|";

input ADDRESS :$20.;

datalines;
1 STREET
#11 STREET
;

run;


proc sql;
create table want as
select * 
from have,ref_table
having startnum<= input(compress(address,,'kd'),8.)<=endnum;
quit;