topic Re: New User - How to sum a column based on criteria from other columns? in New SAS User

New User - How to sum a column based on criteria from other columns?

ohsofaizy — Wed, 09 Jan 2019 21:01:13 GMT

Security	Trading ID	Quantity	Cash
Apple	560	400	10
Apple	561	400	0
Apple	562	200	0
IBM	987	60	10
IBM	435	50	5
Amazon	755	500	10
Amazon	756	500	0
Nike	757	500	0
Samsung	982	100	10
Samsung	983	200	0
Samsung	122	400	10

Based on the table above I am trying to figure out a way in SAS 9.3 (EG 5.1) using proc SQL

Whenever cash > 0 then I would like to group the security and sum 'quantity' if trading IDs are within a range of 12. Ultimately I would like this particular table to come out as:

Security	Quantity
Apple	1000
IBM	60
IBM	50
Amazon	1000
Nike	500
Samsung	300
Samsung	400

Re: New User - How to sum a column based on criteria from other columns?

Reeza — Wed, 09 Jan 2019 21:00:36 GMT

Why is Amazon 1500? What have you tried so far, what does your SQL look like?

EDIT: And why is Samsung in with two enteries, when one is 0?

Re: New User - How to sum a column based on criteria from other columns?

ohsofaizy — Wed, 09 Jan 2019 21:03:03 GMT

I edited Amazon...it should be 1000. Thanks for the heads up.

Samsung has two entries because two of the trading ids are within the range of 12 so must be added together. The third Samsung entry has a trading id which is in not in range of any other trading id so is shown alone.

Re: New User - How to sum a column based on criteria from other columns?

Reeza — Wed, 09 Jan 2019 21:07:21 GMT

@ohsofaizy wrote:
I edited Amazon...it should be 1000. Thanks for the heads up.

Samsung has two entries because two of the trading ids are within the range of 12 so must be added together. The third Samsung entry has a trading id which is in not in range of any other trading id so is shown alone.

What about that cash>0 criteria mentioned in your original post?

Whenever cash > 0

Re: New User - How to sum a column based on criteria from other columns?

ohsofaizy — Wed, 09 Jan 2019 21:13:54 GMT

Since the cash is greater than 0 (10 in this case), and there are no trading ids within a range of 12 the quantity of 400 will remain.

Ultimately the sum of the "quantity" column should still be the same in both tables.

Re: New User - How to sum a column based on criteria from other columns?

Reeza — Wed, 09 Jan 2019 21:13:53 GMT

This should get you started, I'll leave the final steps up to you.

You should double check the join criteria, particularly the 12 boundary value, depending on exactly how that's specified it may need to be 11 since the BETWEEN operator includes the boundary values.

*create sample data to work with;
data have;
informat security $30.;
input Security	$ TradingID	Quantity	Cash;
cards;
Apple	560	400	10
Apple	561	400	0
Apple	562	200	0
IBM	987	60	10
IBM	435	50	5
Amazon	755	500	10
Amazon	756	500	0
Nike	757	500	0
Samsung	982	100	10
Samsung	983	200	0
Samsung	122	400	10
;;;;
run;


*Code to join on itself;
proc sql;
create table summary as 
select h1.security, sum(h2.quantity) as total
from have as h1
left join have as h2
on h1.security = h2.security
/*Trading ID within 12*/
and h1.tradingID between h2.tradingID - 12 and h2.tradingID + 12
where h1.cash>0
group by h1.security, h1.TradingID;
quit;

Re: New User - How to sum a column based on criteria from other columns?

ohsofaizy — Wed, 09 Jan 2019 21:16:07 GMT

Thank you! Let me take a look at this and see if I can figure it out.

Thanks!