Merging Data in Proc SQL

[titanbob]

Getting a Note: NOTE: The query requires remerging summary statistics back with the original data.  Not sure what the issue is with my program why it is not grouping the data correctly. Here is my code:

proc sql;
create table trade_class_5_nc_borrow as
select
date,
asset_agreement,
product,
case when (sourcesystemname = 'Loanet_0250' or inventory = 'T') and product = 'UST' then 'Funding' else 'Non-Cash Borrow' end as new_asset_execution_type,
affiliate,
o_n_term,
maturity_bucket,
sum(allocated_funding_value) as sum_allocated_funding_value

from class_trade_2

where agreement_type = 'Non-Cash Borrow'
group by date, asset_agreement, product, case when (sourcesystemname = 'Loanet_0250' or inventory = 'T') and product = 'UST' then 'Funding' else 'Non-Cash Borrow' end, affiliate, o_n_term, maturity_bucket ;
quit;

Any help is greatly appreciated!

Thanks

[kiranv_]

try using 

calculated new_asset_execution_type

in your group instead of case when

try this. this case when thing works in every other sql implementation, I have used but somehow not in proc sql

proc sql;
create table cars as 
select case when make = 'Acura' then 'Audi'
else make end as newmake, model, sum(msrp) from sashelp.cars
group by calculated newmake , model
;

proc sql;
create table cars as 
select case when make = 'Acura' then 'Audi'
else make end as newmake, model, sum(msrp) from sashelp.cars
group by case when make = 'Acura' then 'Audi'
else make end , model
;

[novinosrin]

I think that would still not stop proc sql to  remerge .'-) Let's wait and see 

@titanbob  Hi, Try running your code with following change in group by

group by date, asset_agreement, product, new_asset_execution_type, affiliate, o_n_term, maturity_bucket ;

[novinosrin · Accepted solution]

A clearer demo using sashelp.class

Log report comparisions:

WANT1

5352 proc sql;
5353 create table want1 as
5354 select sex,case when (age<=12) then (age<=12)*1 else 0 end as a, sum(age) as s
5355 from sashelp.class
5356 group by sex,case when (age<=12) then (age<=12)*1 else 0 end;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.WANT1 created, with 19 rows and 3 columns.

WANT2

5358 proc sql;
5359 create table want2 as
5360 select sex,case when (age<=12) then (age<=12)*1 else 0 end as a, sum(age) as s
5361 from sashelp.class
5362 group by sex,a;
NOTE: Table WORK.WANT2 created, with 4 rows and 3 columns.

[titanbob]

Thanks, that worked !  Appreciate the help!

[PGStats]

To decide whether or not to remerge, SAS doesn't go as far as comparing complex expressions between the select columns and the group by columns. This is reasonable behavior because otherwise SAS/SQL would have to check the logical or mathematical equivalence of expressions, a task with no end.