Desktop productivity for business analysts and programmers

Proc SQL: Group By Formatted Values

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

Proc SQL: Group By Formatted Values

Hi,

Is there any wrong with the below code? I want to group by formatted values. And I am not getting distinct values for the month of 201404.

And let's say I don't want to use SELECT DISTINCT

proc sql;

  select

  customer_number

  ,mismonth

  ,put(pf_account_level_5 ,$pnl.) as account

  ,put((product_level_5),$product.) AS PRODUCT

  ,sum(base_amount) as base_amount

  from CAbal1

  group by customer_number

  , mismonth

  , account

  , PRODUCT

;quit;

Output:

Customer_NumberMisMonthAccountProductbase_amount
ZC00496500X201402AVGLIABSGDCA211036.2
ZC00496500X201402EOPLIABSGDCA210461.4
ZC00496500X201403AVGLIABSGDCA103288.7
ZC00496500X201403EOPLIABSGDCA90446.23
ZC00496500X201404AVGLIABSGDCA88210.63
ZC00496500X201404AVGLIABSGDCA88210.63
ZC00496500X201404EOPLIABSGDCA88596.22
ZC00496500X201405AVGLIABSGDCA88232.96
ZC00496500X201405EOPLIABSGDCA88408.44

Accepted Solutions
Solution
‎11-26-2014 07:18 AM
Grand Advisor
Posts: 17,333

Re: Proc SQL: Group By Formatted Values

Do you have a variable called account/product in your original data set?

Try adding calculated before your aggregated values.

This assumes your query is exactly like follows. If you're keeping other variables besides the one's shown that are not included in the group by clause that's why you have that error.

  1. proc sql; 
  2.   select 
  3.   customer_number 
  4.   ,mismonth 
  5.   ,put(pf_account_level_5 ,$pnl.) as account 
  6.   ,put((product_level_5),$product.) AS PRODUCT 
  7.   ,sum(base_amount) as base_amount 
  8.   from CAbal1 
  9.   group by customer_number 
  10.   , mismonth 
  11.   , calculated account 
  12.   , calculated PRODUCT 
  13. ;quit; 

View solution in original post


All Replies
Respected Advisor
Posts: 3,063

Re: Proc SQL: Group By Formatted Values

What does the log say? Are there any notes about remerging data?

Frequent Contributor
Posts: 90

Re: Proc SQL: Group By Formatted Values

NOTE: The query requires remerging summary statistics back with the original data.

I suspect it go to do with put(pf_account_level_4 ,$pnl.) as account

I could first create a table and a column Account = put(pf_account_level_4 ,$pnl.)

Then trying grouping again, but I prefer doing in one step.

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Proc SQL: Group By Formatted Values

Hi,


Well it would be easier with some test data to work with.  I assume it works ok if you do put distinct before?

Maybe update to:

proc sql;

     create table WANT as

     select     CUSTOMER_NUMBER,

                    MISMONTH,

                    ACCOUNT,

                    PRODUCT,

                    SUM(BASE_AMOUNT) as AMOUNT

     from        (select CUSTOMER_NUMBER,                         /* so if you want to see your actual data you only need this bit */

                                MISMONTH,

                                put(PF_ACCOUNT_LEVEL_5,$pnl.) as ACCOUNT,

                                put(PRODUCT_LEVEL,$product.) as PRODUCT

                     from   CABAL1)

     group by MISMONTH,  /* Do you not want to group by CUSTOMER_NUMBER?  If not remove from select as there are multiple

                                              CUSTOMER_ID's per MISMONTH */

                    ACCOUNT,

                    PRODUCT;

quit;

Grand Advisor
Posts: 17,333

Re: Proc SQL: Group By Formatted Values

Is mismonth a formatted date variable?

The other possibility is that one of your text field has an invisible character so it looks the same to the naked eye, but is actually a different value to the computer.

Try a proc means to see if you get the same results:

proc sort data=cabal1; by

customer_number mismonth pf_account_level_5 product_level_5;

run;

proc means data=CAbal1 noprint;

BY customer_number mismonth pf_account_level_5 product_level_5;

format pf_account_level_5 $pnl. product_level_5 $product.;

output out=want sum(base_amount)=base_amount;

run;

Frequent Contributor
Posts: 90

Re: Proc SQL: Group By Formatted Values

mismonth is a numeric value.

There is no invisible character in the text field. Because it works with SELECT DISTINCT.

Solution
‎11-26-2014 07:18 AM
Grand Advisor
Posts: 17,333

Re: Proc SQL: Group By Formatted Values

Do you have a variable called account/product in your original data set?

Try adding calculated before your aggregated values.

This assumes your query is exactly like follows. If you're keeping other variables besides the one's shown that are not included in the group by clause that's why you have that error.

  1. proc sql; 
  2.   select 
  3.   customer_number 
  4.   ,mismonth 
  5.   ,put(pf_account_level_5 ,$pnl.) as account 
  6.   ,put((product_level_5),$product.) AS PRODUCT 
  7.   ,sum(base_amount) as base_amount 
  8.   from CAbal1 
  9.   group by customer_number 
  10.   , mismonth 
  11.   , calculated account 
  12.   , calculated PRODUCT 
  13. ;quit; 
Respected Advisor
Posts: 3,063

Re: Proc SQL: Group By Formatted Values

What SAS version are you using? I have a defect open with SAS R & D for SAS 9.4 regarding GROUP BY queries working differently in SAS 9.4 than in SAS 9.3, and the symptom is the log remerge note appears in 9.4 but not 9.3 and you get duplicate rows.

If you have access to another SAS version I would test to see if you get the same problem.

Frequent Contributor
Posts: 90

Re: Proc SQL: Group By Formatted Values

I am using SAS 9.2

Respected Advisor
Posts: 3,063

Re: Proc SQL: Group By Formatted Values

OK, so not my defect then. Have you tried Reeza's suggestion of adding CALCULATED?

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 1757 views
  • 1 like
  • 4 in conversation