SAS vlookup - view all data not only joined

Question

I would like to see all the data from "one" dataset. If join between tables not exist overwrite the value 0. The current code gives me values only where there is a connection. This table I need:

    data one;
  input lastname: $15. typeofcar: $15. mileage;
datalines;
Jones Toyota 3000
Smith Toyota 13001
Jones2 Ford 3433
Smith2 Toyota 15032
Shepherd Nissan 4300
Shepherd2 Honda 5582
Williams Ford 10532
;

data two;
  input startrange endrange typeofservice & $35.;
datalines;
3000 5000 oil change
5001 6000 overdue oil change
6001 8000 oil change and tire rotation
8001 9000 overdue oil change
9001 11000 oil change
11001 12000 overdue oil change
12001 14000 oil change and tire rotation
15032 14999 overdue oil change
13001 15999 15000 mile check
;


data combine;
do until (mileage<15000);
  set one;
  do i=1 to nobs;
     set two point=i nobs=nobs;
     if startrange = mileage then
        output;
  end;
  end;

run;


proc print;
run;

Description of the code from the SAS support site: Read the first observation from the SAS data set outside the DO loop. Assign the FOUND variable to 0. Start the DO loop reading observations from the SAS data set inside the DO loop. Process the IF condition; if the IF condition is true, OUTPUT the observation and set the FOUND variable to 1. Assigning the FOUND variable to 1 will cause the DO loop to stop processing because of the UNTIL (FOUND) that is coded on the DO loop. Go back to the top of the DATA step and read the next observation from the data set outside the DO loop and process through the DATA step again until all observations from the data set outside the DO loop have been read.

You don't actually state what your join criteria is. Looks like you want to match if milage between startrange and endrange. — Tom
Why do you want zero instead of missing values when there is no match? — Tom
Are you concerned that you might match multiple observations in the lookup table? — Tom
Why does Smith's Toyota @ 13,001 miles not also get a notice for the need to have "oil change and tire rotation". Is 15,000 mile service being recommended starting at mileage 13,001 a typo or a clue to unhappy customers ? — Richard

stallingOne stallingOne · Accepted Answer · 2019-04-08T09:53:21

You could do that with a LEFT JOIN in a proc sql

taking all variables from one then making 2 conditions to fill startrange and endrange with 0 when missing.

proc sql noprint;
    create table want as
    select t1.*
        , case when t2.startrange=. then 0 else t2.startrange end as startrange
        , case when t2.endrange=. then 0 else t2.endrange end as endrange
        , t2.typeofservice
    from one t1 left join two t2
    on (t1.mileage = t2.startrange)
    ;run;quit;

Or do it in 2 steps (I personally find the if of the data step cleaner than the case when of the proc sql.)

proc sql noprint;
    create table want as select *
    from one t1 left join two t2 on (t1.mileage = t2.startrange)
;run;quit;
data want; set want;
    if startrange=. then do; startrange=0; endrange=0; end;
run;

SAS vlookup - view all data not only joined

3 Answers