Desktop productivity for business analysts and programmers

How to summarize a table with the query builder?

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

How to summarize a table with the query builder?

I want to summarize information that i have by client ID. I have a variable number of rows per client ID, so what i want is to get total by client variables.

How do i condense my database to obtain an only Client ID per row? I don't mind losing the date format columns.

When using the Query Builder, i summarize and select by group "ClientID" but my variables became a constant sum next to the same number of rows per client ID as shown below. The values are correct but they self repeat to the number of ClientID rows.

Client ID          date_to_pay      sum_a          sum_b               pay_date               number_week_pay

153495806      30NOV2011      $19,071.82      $12,498.63      01DEC2011      7.1066666667

153495806      15DEC2011      $19,071.82      $12,498.63      17DEC2011      7.1066666667

153495806      31DEC2011      $19,071.82      $12,498.63      14JAN2012      7.1066666667

153981795      31DEC2008      $8,708.31      $7,628.09      05JAN2009      2.1860465116

153981795      15JAN2009      $8,708.31      $7,628.09      16JAN2009     2.1860465116

153981795      31JAN2009      $8,708.31      $7,628.09      16JAN2009      2.1860465116

154499681      31JAN2011      $3,802.16      $2,654.33      31JAN2011      9.2916666667

154499681      30APR2011      $3,802.16      $2,654.33      01MAY2011      9.2916666667

154499681      30JUN2011      $3,802.16      $2,654.33      06AUG2011      9.2916666667

154499681      15JUL2011      $3,802.16      $2,654.33      06AUG2011      9.2916666667

  I would appreciate your help.


Accepted Solutions
Solution
‎10-05-2012 03:02 PM
Community Manager
Posts: 2,697

Re: How to summarize a table with the query builder?

I'm guessing that you grouped by too many variables.  You want to group only by Client_ID, and not by date_to_pay, etc.

When you copy the non-summarized columns to the query output, the Query Builder lumps them in with the "Automatic select groups" results.  It's probably best if you design this query with just two outputs: Client_ID and the column(s) you want to summarize at the Client_ID level.

If you need to use this summarized result for another more complete result, you can Join it back with the detail data as needed, or perhaps your scenario falls into this situation:

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

Chris

View solution in original post


All Replies
Solution
‎10-05-2012 03:02 PM
Community Manager
Posts: 2,697

Re: How to summarize a table with the query builder?

I'm guessing that you grouped by too many variables.  You want to group only by Client_ID, and not by date_to_pay, etc.

When you copy the non-summarized columns to the query output, the Query Builder lumps them in with the "Automatic select groups" results.  It's probably best if you design this query with just two outputs: Client_ID and the column(s) you want to summarize at the Client_ID level.

If you need to use this summarized result for another more complete result, you can Join it back with the detail data as needed, or perhaps your scenario falls into this situation:

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

Chris

Contributor
Posts: 36

Re: How to summarize a table with the query builder?

Thanks Chris, that was exactly the issue. It all worked fine, i appreciate the quick response.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 222 views
  • 0 likes
  • 2 in conversation