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

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
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

9 REPLIES 9
SASKiwi
PROC Star

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

hellind
Quartz | Level 8

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Reeza
Super User

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;

hellind
Quartz | Level 8

mismonth is a numeric value.

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

Reeza
Super User

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; 
SASKiwi
PROC Star

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.

hellind
Quartz | Level 8

I am using SAS 9.2

SASKiwi
PROC Star

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

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
  • 9 replies
  • 7438 views
  • 1 like
  • 4 in conversation