Proc SQL simplification?

dsbihill — Thu, 28 Aug 2014 14:39:06 GMT

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;

Re: Proc SQL simplification?

esjackso — Thu, 28 Aug 2014 14:52:31 GMT

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

Re: Proc SQL simplification?

CTorres — Thu, 28 Aug 2014 15:09:26 GMT

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

Re: Proc SQL simplification?

dsbihill — Thu, 28 Aug 2014 15:21:42 GMT

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

topic Proc SQL simplification? in SAS Programming

Proc SQL simplification?

Re: Proc SQL simplification?

Re: Proc SQL simplification?

Re: Proc SQL simplification?