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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.