Help with Hierarchical / Case Joins in SAS

[jp]

Hi there,

I'm wondering if anyone has encountered a similar problem to the one listed below or knows of a better way to perform the hierarchical joins.

In the example below I want to test whether there is a CoverOption match in each table, and if there isn't I want use a catch all / default value (Cover Code of 10014).

Using a CASE statement in the join, followed by taking the first id value solves this, but this works when the Default Cover code is always at the end of the range (which might not always be the case).

Is there any way to perform the steps below in a better way? Using IF-THEN-ELSE logic in a Data Step merge statement perhaps?

Please note that this is a simplified case, in reality there are about 7 keys which need to be merged and have some kind of default value which needs to be checked.

Thanks for any insight.

data Rates;

infile datalines dlm='09'x;

input VehicleType CoverType Rate_1 Rate_2;

datalines;

10002 10014 0.00076     0.00000

10006 10014 0.00000     6.08000

10008 10014 0.00076     0.00000

10010 10014 0.00000     6.08000

10011 10014 0.00076     0.00000

10012 10014 0.00076     0.00000

10017 10014 0.00000     6.08000

10023 10014 0.00000     6.08000

10034 10013 0.00000     0.00000

10034 10014 0.00000     6.08000

10035 10014 0.00076     0.00000

;

run;

data Data;

infile datalines dlm='09'x;

input id VehicleType CoverType;

datalines;

1     10006 10010

2     10034 10013

;

run;

PROC SQL;

      create table join as

      select     

          Data.*

     ,    Rates.*

     ,    Rates.VehicleType       as Rates_VehicleType

     ,    Rates.CoverType         as Rates_CoverType

      from  Data as Data

                        left join

         Rates as Rates

         on Data.VehicleType = Rates.VehicleType

                  and

         CASE

              WHEN Data.CoverType = Rates.CoverType

              THEN Data.CoverType = Rates.CoverType

              ELSE Rates.CoverType = 10014

         END

      order       by id

         ,     Rates.CoverType

;QUIT;

data final;

      set join;

      by id  ;

      if first.id;

run;

[PGStats]

I'm not sure I understand what you need but I assumed you wanted to get the rates for every vehicle type and cover type from a table and use some default cover and rates for combinations you can't find. I propose to split your information into 3 tables: Types (vehicle, cover), Rates (vehicle, cover, rates) and Defaults (vehicle, default cover, default rates)

and combine them this way:

data Rates;
input vehicleType coverType rate_1 rate_2;
datalines;
10034 10013 0.00000     0.00000
;

data defaults;
input vehicleType coverType rate_1 rate_2;
datalines;
10002 10014 0.00076     0.00000
10006 10014 0.00000     6.08000
10008 10014 0.00076     0.00000
10010 10014 0.00000     6.08000
10011 10014 0.00076     0.00000
10012 10014 0.00076     0.00000
10017 10014 0.00000     6.08000
10023 10014 0.00000     6.08000
10034 10014 0.00000     6.08000
10035 10014 0.00076     0.00000
;

data Types;
input id vehicleType coverType;
datalines;
1     10006 10010
2     10034 10013
;

proc sql;
create table TypeRates as
select 
     T.id,
     T.vehicleType,
     coalesce(R.coverType, D.coverType) as coverType,
     case
          when R.coverType is missing then D.rate_1
          else R.rate_1
          end as rate_1 format=8.5,
     case
          when R.coverType is missing then D.rate_2
          else R.rate_2
          end as rate_2 format=8.5
from
     Types as T left join
     Rates as R on T.vehicleType=R.vehicleType and T.coverType=R.coverType left join
     Defaults as D on T.vehicleType=D.vehicleType;
select * from TypeRates;
quit;

PG

[jp]

Apologies for the lack of clarity.

Basically I need a way to use some logic to determine the correct join criteria used in a query.

The logic should try to match on values where they exist, but if they don't exist it needs to use some predetermined default values. I've tried to perform this "hierarchical" join (for lack of a better term) using Case Statements in the join. The join logic I'm really trying to achieve is listed below:

IF Data.VehicleType in Rates.VehicleType AND Data.CoverType in Rates.CoverType

     THEN Data.VehicleType = Rates.VehicleType AND Data.CoverType = Rates.CoverType

ELSE IF Data.VehicleType in Rates.VehicleType AND Data.CoverType NOT in Rates.CoverType

     THEN Data.VehicleType = Rates.VehicleType AND Rates.CoverType = <default>

ELSE IF Data.VehicleType NOT in Rates.VehicleType AND Data.CoverType in Rates.CoverType

     THEN Rates.VehicleType = <default> AND Data.CoverType = Rates.CoverType

ELSE IF Data.VehicleType NOT in Rates.VehicleType AND Data.CoverType NOT in Rates.CoverType

     THEN Rates.VehicleType = <default> AND Rates.CoverType = <default>

A coalesce function won't work because the values are always populated but they just don't match up.

My solution works, but it feels really clunky, and I'm frustrated that I can't perform a join that follows the logic above. Other solutions include using a Full Join, and then logic to remove unwanted rows, but with a large number of Customers in the "Data" dataset I doubt that this is the optimal solution.

I'm not sure that your solution scales to the scenario with multiple rate tables, each with similar/different keys and default values. Also in your defaults table there would be one default value for the Vehicle Types so perhaps the tables would need to be switched around, but I'm not convinced this is optimal.

Thanks for your time and response.

[Ksharp]

If I were you , I would like use HashTable.