BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

## Proc SQL simplification?

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Quartz | Level 8

## Re: Proc SQL simplification?

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

3 REPLIES 3
Quartz | Level 8

## Re: Proc SQL simplification?

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

Quartz | Level 8

## Re: Proc SQL simplification?

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

Obsidian | Level 7

## Re: Proc SQL simplification?

Thank you all, worked perfectly had not heard of that function.

Discussion stats
• 3 replies
• 1147 views
• 3 likes
• 3 in conversation