topic Re: Finding the maximum value based on a customer ID in SAS Enterprise Guide

Finding the maximum value based on a customer ID

saltmarsh — Tue, 26 Mar 2013 15:04:49 GMT

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.

Re: Finding the maximum value based on a customer ID

ChrisHemedinger — Tue, 26 Mar 2013 15:08:11 GMT

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

Re: Finding the maximum value based on a customer ID

Haikuo — Tue, 26 Mar 2013 15:20:30 GMT

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

Haikuo

Re: Finding the maximum value based on a customer ID

saltmarsh — Tue, 26 Mar 2013 17:22:02 GMT

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.

Re: Finding the maximum value based on a customer ID

LinusH — Tue, 26 Mar 2013 19:10:15 GMT

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).

Re: Finding the maximum value based on a customer ID

ChrisHemedinger — Tue, 26 Mar 2013 19:15:20 GMT

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.

Re: Finding the maximum value based on a customer ID

saltmarsh — Tue, 26 Mar 2013 20:58:58 GMT

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.