I have a lookup being provided to me in the following format. data test2;
input code1 $6. code2 $6. data;
datalines;
23145 23147 5
7892F 7892F 6
7893F 7893F 6
32012 32012 7
4456G 4456G 8
80090 80091 9
; I have data structured like this. data test1;
input code $5.;
datalines;
80090
80091
7892F
7893F
7893F
32012
4456G
23145
23146
23147
; I had previously been trying to join data in the following way, which creates a cartesian join and I'm unable to execute on our large dataset. proc sql;
create table want as
select a.*,
b.data
from test1 a left join test2 b on
(a.code <= b.code2)
And
(a.code >= b.code1)
;
quit; To remedy this, I was trying to figure out a way to "flatten" this lookup so that I would have the following. data want2;
input code1 $6. data;
datalines;
23145 5
23146 5
23147 5
7892F 6
7893F 6
32012 7
4456G 8
80090 9
80091 9
; Essentially trying to spell out all the values that would be in the ranges and create a unique line for each. How could I create want2? To my current knowledge, none of the lookup values that contain letters would have a range, i.e., the code1 and code2 values would match. But if there's a way to create a robust process that could account for future changes from this I would be very interested. I'm also open to thoughts on how to optimize the join (create want directly), lookup or anyway to complete.
... View more