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

I would like to sum a column, "Sales", with the following conditions:

 

  1. Sales amounts does not equal amount in "Returns" column.
  2. Sales amounts does not equal amounts in "Rebates" column.

Can I do this with a Computed Column in Enterprise Guide?  If not, please help with code.  Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Fistful_Dollars
Obsidian | Level 7

Thank you all for the great help.

 

I ended up using something similar to FredrikE, but with an "OR".  I also added another WHEN statement I needed.

 

CASE
     WHEN ((Return_Amt + Store_Credit) = Sale_Amt)
          THEN Return_Amt
     WHEN Sale_Amt ne Return_Amt AND Sale_Amt ne Rebate_Amt
          THEN Sale_Amt
ELSE 0 END

 

Then I sum the column in the next query.

 

For some reason when I put the first "WHEN" statement with the formula below the other, the Sale_Amt would populate, not the Return_Amt.  I suppose the first "THEN" takes precedence on the CASE statement.  Thank you, everyone.

View solution in original post

8 REPLIES 8
Reeza
Super User

@Fistful_Dollars wrote:

 

Can I do this with a Computed Column in Enterprise Guide?  If not, please help with code.  Thank you.


Yes you can use computed columns for this. 

Create two columns using CASE statements, one for condition #1 and one for condition #2. 

Then sum each of those new columns. 

 

 

 

 

kiranv_
Rhodochrosite | Level 12

just expanding on what @Reeza suggested

 

sum( case when sales_amount ne Returns then sales_amount else 0 end) as computed_column1,

sum( case when sales_amount ne Rebates then sales_amount else 0 end) as computed_column2

Reeza
Super User

@kiranv_One of those should be eq not ne 🙂

FredrikE
Rhodochrosite | Level 12

Why use two columns?

Why not just add this condition in the computed colums editor and then select aggregation sum?

 

case

  when (Sales_amounts ne Returns OR Sales_amounts ne Rebates) then sales_amount)

  else 0

end

 

Depending on the logic use AND instead of OR...

 

//Fredrik

Reeza
Super User

@FredrikE is correct, I was reading that as creating two different sums. If it's a single sum you can definitely use the OR condition within the CASE statement.

Fistful_Dollars
Obsidian | Level 7

Thank you all for the great help.

 

I ended up using something similar to FredrikE, but with an "OR".  I also added another WHEN statement I needed.

 

CASE
     WHEN ((Return_Amt + Store_Credit) = Sale_Amt)
          THEN Return_Amt
     WHEN Sale_Amt ne Return_Amt AND Sale_Amt ne Rebate_Amt
          THEN Sale_Amt
ELSE 0 END

 

Then I sum the column in the next query.

 

For some reason when I put the first "WHEN" statement with the formula below the other, the Sale_Amt would populate, not the Return_Amt.  I suppose the first "THEN" takes precedence on the CASE statement.  Thank you, everyone.

FredrikE
Rhodochrosite | Level 12

As soon as a case/when evaluation is true it stops the evaluation, so more important "when"'s should come first 🙂

//Fredrik

kiranv_
Rhodochrosite | Level 12

@Reeza you are right, looks like I did not understand the question properly

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 8 replies
  • 3045 views
  • 3 likes
  • 4 in conversation