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

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
esjackso
Quartz | Level 8

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

View solution in original post

3 REPLIES 3
esjackso
Quartz | Level 8

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
Quartz | Level 8

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
Obsidian | Level 7

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

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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