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.
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
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
Thanks Chris, that was exactly the issue. It all worked fine, i appreciate the quick response.
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.
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.