CODEWITHSUNDEEP
sasvlookupsas-macro
Wioleta Zgliczyńska
Wioleta Zgliczyńska

Reputation: 57

SAS vlookup - view all data not only joined

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:

enter image description here

    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.

Upvotes: 0

Views: 237

Answers (3)

Tom
Tom

Reputation: 51621

Data step is not the best way to code this. It is much easier to code fuzzy matches using SQL code.

Not sure why you need to have zeros instead of missing values, but coalesce() should make it easy to provide them. 

proc sql ;
  create table combine as
    select a.*
         , coalesce(b.startrange,0) as startrange
         , coalesce(b.endrange,0) as endrange
         , b.typeofservice
    from one a left join two b
    on a.mileage between b.startrange and b.endrange
  ;
quit;

Upvotes: 0

Wioleta Zgliczyńska
Wioleta Zgliczyńska

Reputation: 57

I can't use proc sql because I need Vlookup inside loop UNTIL. I need another solution.

Upvotes: 0

stallingOne
stallingOne

Reputation: 4006

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;

Upvotes: 0

Related Questions