topic Re: How to summarize a table with the query builder? in SAS Enterprise Guide

How to summarize a table with the query builder?

CesarOmarHR — Fri, 05 Oct 2012 18:43:30 GMT

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.

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

ChrisHemedinger — Fri, 05 Oct 2012 19:02:45 GMT

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

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

CesarOmarHR — Fri, 05 Oct 2012 21:09:20 GMT

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