Summation for more than once of the dataset using proc sql

[meichan1128]

data mydata;
input 
Category $ 
Item 
type
amount;
datalines;
A  1 100 11111
A  2 800 11111
A  3 123 11111
B  1 113 11111
B  2 800 11111
C  1 111 11111
C  2 800 11111
;

My attempt

proc sql;
create table want as 
    select *, sum(amount and item <> 900) as without900, sum(amount) as total from mydata       
group by category
;
quit;

Result

Category    Item    type    amount  without900  total  
  A          3       123     11111       3      33333
  A          1       100     11111       3      33333
  A          2       900     11111       3      33333
  B          2       900     11111       2      22222
  B          1       113     11111       2      11111
  C          2       900     11111       2      11111
  C          1       111     11111       2      11111

Expected result

Category    Item    type    amount  without900  total  
  A          3       123     11111    22222     33333
  A          1       100     11111    22222     33333
  A          2       900     11111    22222     33333
  B          2       900     11111    11111     22222
  B          1       113     11111    11111     11111
  C          2       900     11111    11111     11111
  C          1       111     11111    11111     11111

I know this can be easily achieved by creating another table and maybe hence using left join. I wonder how to achieve the expected using as least proc SQL step as possible. Thank you very much.

[Kurt_Bremser · Accepted solution]

This

amount and item <> 900

is a condition. Since all your items are not equal to 900, the second part is always true; amount is also always not missing and not zero, so the whole condition will always result in true, which is a numeric 1. The sum of that per group is what you see.

Did you actually want to check for type, and sum depending on that?

sum(case when type ne 900 then amount else 0 end)

If you want to build some kind of a rolling sum, the data step is the tool of choice.

[meichan1128]

yes, I want to check for type, sorry for the typo and confusion. Appreciate so much for your help, it works great and looks simple. I was thinking of using subquery.

[Kurt_Bremser]

CASE expressions are the SQL equivalent of IF/THEN/ELSE in other languages.

[Kurt_Bremser]

PS the type values in your result differ from the input data.

[novinosrin]

Hi @meichan1128   Looking at your question and gauging your attempt to solve with an expression offers an insight to look into "boolean expressions" as math or "conditional expressions" as check. The thumb rule for expressions, whether simple or compound must/will have to evaluate to 'true' or 'false' or numerically speaking 1 or 0. 

In your example a simple arithmetic boolean expression will work by having to multiply the amount to 1(true) expressions -

sum(amount* (type <> 900)) as without900

data mydata;
input 
Category $ 
Item 
type
amount;
datalines;
  A          3       123     11111       3      33333
  A          1       100     11111       3      33333
  A          2       900     11111       3      33333
  B          2       900     11111       2      22222
  B          1       113     11111       2      11111
  C          2       900     11111       2      11111
  C          1       111     11111       2      11111
;

proc sql;
create table want as 
select *, sum(amount* (type <> 900)) as without900, sum(amount) as total 
from mydata       
group by category;
quit;

The same of course is accomplished by using conditional construct such as CASE WHEN expressions and  IFC/IFN functions. 

/*IFN*/
proc sql;
create table want as 
select *, sum(ifn(type <> 900,amount,0)) as without900, sum(amount) as total 
from mydata       
group by category;
quit;
/*CASE WHEN*/
proc sql;
create table want as 
select *, sum(case when type<>900 then amount else 0 end) as without900, sum(amount) as total 
from mydata       
group by category;
quit;

IFC/IFN functions are SAS functions and you cannot quite expect to have great performance and will likely pay a penalty when used with SQL particularly when trying to push the query into the database by the SQL engine. However, it really does offer convenience from a coding standpoint.

CASE WHEN expression is a safe bet for the reason it's universal and is ANSI SQL compliant making it highly portable and works in almost all RDBMS systems. I hope this helps.

Best Regards!