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.
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.
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
Depending on how your data is structured, it could have different approach. What does your table look like?
Haikuo
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.
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).
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.