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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 1010 views
  • 3 likes
  • 3 in conversation