Proc SQL simplification?

[dsbihill]

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;

[esjackso · Accepted solution]

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

[CTorres]

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

[dsbihill]

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