I am still fairly new to SQL and I am trying to learn to be more efficient in the code I write. Below is code that works to combine 2 tables to compare costs. I had to write code for this full join to create a single column for customer, gd, apg1, and usage_type to compensate for either table not having that information. Is there an easier way to do this.
Thank you so much for your help
PROC SQL; /*Combine Actual to model and caclulate cost, compensate for missing values between record sets */
CREATE TABLE temp_Cost_V as
SELECT
Case
when mod.customer=' ' then act.customer
else mod.customer
end as Customer,
Case
when mod.gd=' ' then act.gd
else mod.gd
end as gd,
case
when mod.apg1=' ' then act.apg1
else mod.apg1
end as APG1,
case
when mod.usage_type=' ' then act.golden_usage_type
else mod.usage_type
end as usage_type,
mod.Calculated_freq as mod_freq, act.trans_qty as act_freq, sum(act.trans_qty, -mod.calculated_freq) as qty_v, mod.calculated_ext_aco as mod_aco, act.Trans_cost,
sum(act.Trans_cost, -mod.Calculated_ext_aco) format dollar16. AS CostV
FROM WORK.tmp_user_mod mod
FULL JOIN WORK.TEMP_ACTUAL_TOTALS_BY_APG act ON (mod.customer = act.CUSTOMER) AND
(mod.gd = act.GD) AND (mod.apg1 = act.apg1) AND (mod.Usage_type = act.golden_usage_type)
order by customer, gd, apg1
;
QUIT;
You might want to take a look at the coalesce function to see if that would work for you:
SAS(R) 9.4 SQL Procedure User's Guide
EJ
You might want to take a look at the coalesce function to see if that would work for you:
SAS(R) 9.4 SQL Procedure User's Guide
EJ
I think you can replace the case clauses using the coalesce function:
coalesce (mod.customer, act.customer) as customer,
coalesce (mod.gd, act.gd) as gd,
coalesce (mod.agp1, act.agp1) as agp1,
coalesce (mod.usage_type, act.usage_type) as usage_type,
CTorres
Thank you all, worked perfectly had not heard of that function.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: