topic Proc SQL: Group By Formatted Values in SAS Enterprise Guide

Proc SQL: Group By Formatted Values

hellind — Wed, 26 Nov 2014 03:35:21 GMT

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_Number	MisMonth	Account	Product	base_amount
ZC00496500X	201402	AVGLIAB	SGDCA	211036.2
ZC00496500X	201402	EOPLIAB	SGDCA	210461.4
ZC00496500X	201403	AVGLIAB	SGDCA	103288.7
ZC00496500X	201403	EOPLIAB	SGDCA	90446.23
ZC00496500X	201404	AVGLIAB	SGDCA	88210.63
ZC00496500X	201404	AVGLIAB	SGDCA	88210.63
ZC00496500X	201404	EOPLIAB	SGDCA	88596.22
ZC00496500X	201405	AVGLIAB	SGDCA	88232.96
ZC00496500X	201405	EOPLIAB	SGDCA	88408.44

Re: Proc SQL: Group By Formatted Values

SASKiwi — Wed, 26 Nov 2014 04:19:29 GMT

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

Re: Proc SQL: Group By Formatted Values

Reeza — Wed, 26 Nov 2014 04:39:55 GMT

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;

Re: Proc SQL: Group By Formatted Values

hellind — Wed, 26 Nov 2014 10:58:39 GMT

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.

Re: Proc SQL: Group By Formatted Values

hellind — Wed, 26 Nov 2014 11:05:32 GMT

mismonth is a numeric value.

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

Re: Proc SQL: Group By Formatted Values

RW9 — Wed, 26 Nov 2014 11:20:21 GMT

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;

Re: Proc SQL: Group By Formatted Values

Reeza — Wed, 26 Nov 2014 12:18:08 GMT

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.

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
, calculated account
, calculated PRODUCT
;quit;

Re: Proc SQL: Group By Formatted Values

SASKiwi — Wed, 26 Nov 2014 18:42:51 GMT

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.

Re: Proc SQL: Group By Formatted Values

hellind — Thu, 27 Nov 2014 02:04:28 GMT

I am using SAS 9.2

Re: Proc SQL: Group By Formatted Values

SASKiwi — Thu, 27 Nov 2014 03:39:32 GMT

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