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 |
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.
What does the log say? Are there any notes about remerging data?
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.
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;
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;
mismonth is a numeric value.
There is no invisible character in the text field. Because it works with SELECT DISTINCT.
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.
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.
I am using SAS 9.2
OK, so not my defect then. Have you tried Reeza's suggestion of adding CALCULATED?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.