BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CesarOmarHR
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

2 REPLIES 2
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
CesarOmarHR
Calcite | Level 5

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

sas-innovate-2024.png

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.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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