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;
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
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.
If I were you , I would like use HashTable.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.