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

Using Enterprise Guide 4.2, I am trying to group data under one value where a customer will have one Customer ID but two locations. I'd like to be able to use the account with the largest balance within a customer id to determine which location to use for all accounts. For example,

Cust ID 1

Acct 1

balance - $1,000

Location - RI

Acct 2

balance -$2,000

Location - DC

I'd like the report to show the customer location as DC based on the fact that for Cust ID x, the account with $2,000 is the largest balance, therefore the location will be DC. The end goal will be to have this grouped on one line.

Thanks for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

I agree with - I should have point out in my response that the GROUP/HAVING technique (in the blog post) requires you to "uncheck" automatic grouping in the query builder to get the result you want.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

6 REPLIES 6
ChrisHemedinger
Community Manager

You should be able to achieve this in the Query Builder, using Grouping with the MAX aggregation.  See this blog post for a similar problem/solution:

HAVING (clause) fun with SAS Enterprise Guide - The SAS Dummy

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Haikuo
Onyx | Level 15

Depending on how your data is structured, it could have different approach. What does your table look like?

Haikuo

saltmarsh
Calcite | Level 5

Thank you both.

With respect to the Max, this works as long as I don't have any other data that can cause the results to split again. I have some work to do on my grouping as it seems that I have to do multiple groups to make this work. Perhaps I should do the max earlier in an earlier query as opposed to later?This is one of the reasons I was looking for some sort of computed field.

With respect to the table type. It's a flat file nothing too strange.

Thank you again.

LinusH
Tourmaline | Level 20

With the information you provided it seems you could do this in one step.

Group by on cust id alone (not default grouping as suggested by EG).

Having balance = max(balance).

Data never sleeps
ChrisHemedinger
Community Manager

I agree with - I should have point out in my response that the GROUP/HAVING technique (in the blog post) requires you to "uncheck" automatic grouping in the query builder to get the result you want.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
saltmarsh
Calcite | Level 5

Thank you all. I was able to make it work based on your advice. One of the issues that I was having was Grouping in the wrong query. Once I figured that out, it works as advertised.

Thank you again for your help.

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
  • 6 replies
  • 990 views
  • 6 likes
  • 4 in conversation