topic Re: Merging Data in Proc SQL in New SAS User

Merging Data in Proc SQL

titanbob — Fri, 26 Oct 2018 16:38:15 GMT

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

Re: Merging Data in Proc SQL

kiranv_ — Fri, 26 Oct 2018 17:17:25 GMT

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
;

Re: Merging Data in Proc SQL

novinosrin — Fri, 26 Oct 2018 17:21:04 GMT

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 ;

Re: Merging Data in Proc SQL

PGStats — Fri, 26 Oct 2018 17:50:40 GMT

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.

Re: Merging Data in Proc SQL

novinosrin — Fri, 26 Oct 2018 18:31:56 GMT

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.

Re: Merging Data in Proc SQL

titanbob — Fri, 26 Oct 2018 19:35:02 GMT

Thanks, that worked ! Appreciate the help!