How to find number of buy or sell order observation on a daily basis?

[bd_user_10]

Hi Sas Community,

I have the following dataset:

Date                      SEDOL                   Buy_sell               Buy_Vol              Sell_Vol

2007-08-17          440273                  10                           1200                       0

2007-08-17          440273                  10                           800                         0

2007-08-17          440273                  10                           1000                       0

2007-08-17          440273                  10                           800                         0

2007-08-17          440273                  10                           1200                       0

2007-08-17          440273                  20                           0                              200

2007-08-18          450576                  20                           0                              3000

2007-08-18          450576                  20                           0                              5000

2007-08-18          450576                  20                           0                              2000

2007-08-19          450576                  10                           800                         0

and i want to do the following:

Date                      SEDOL         Buy_Vol            Sell_Vol       No of_buy_order       No of_sell_order

2007-08-17          440273         5000                      200                        5                                      1

2007-08-18          450576         0                            10000                    0                                      3

2007-08-19          450576         800                         800                       1                                      0

Any help will be appreciated. Thanks.

[Kurt_Bremser]

"Community Matters" is for issues concerning the working of the SAS Communities. I moved your question to the proper SAS Programming community.

Try this

proc sql;
create table want as
select
  date,
  sedol,
  sum(buy_vol) as buy_vol,
  sum(sell_vol) as sell_vol,
  sum(case
    when buy_vol be 0 then 1
    else 0
  end) as no_of_buy_order
  sum(case
    when sell_vol ne 0 then 1
    else 0
  end) as no_of_sell_order
from have
group by date, sedol;
quit;

[bd_user_10]

Thanks Kurt! there are few small issues in the codes, e.g.' 'be' needs to be 'ne', before the last sum there needs to be a ','. However, for some reason no_of_buy_order and no_of_sell_order are exactly the same in my new table. I cannot figure out why that is the case. Am i missing anything?

[Kurt_Bremser · Accepted solution]

When I run

data have;
input Date :yymmdd10.                     SEDOL                   Buy_sell               Buy_Vol              Sell_Vol;
cards;
2007-08-17          440273                  10                           1200                       0
2007-08-17          440273                  10                           800                         0
2007-08-17          440273                  10                           1000                       0
2007-08-17          440273                  10                           800                         0
2007-08-17          440273                  10                           1200                       0
2007-08-17          440273                  20                           0                              200
2007-08-18          450576                  20                           0                              3000
2007-08-18          450576                  20                           0                              5000
2007-08-18          450576                  20                           0                              2000
2007-08-19          450576                  10                           800                         0
;
run;

proc sql;
create table want as
select
  date,
  sedol,
  sum(buy_vol) as buy_vol,
  sum(sell_vol) as sell_vol,
  sum(case
    when buy_vol ne 0 then 1
    else 0
  end) as no_of_buy_order,
  sum(case
    when sell_vol ne 0 then 1
    else 0
  end) as no_of_sell_order
from have
group by date, sedol;
quit;

proc print data=want noobs;
format date yymmddd10.;
run;

I get

                                                            no_of_
                                                 no_of_      sell_
      Date     SEDOL    buy_vol    sell_vol    buy_order     order

2007-08-17    440273      5000         200         5           1  
2007-08-18    450576         0       10000         0           3  
2007-08-19    450576       800           0         1           0  

[PaigeMiller]

---

@bd_user_10 wrote:
However, for some reason no_of_buy_order and no_of_sell_order are exactly the same in my new table. I cannot figure out why that is the case. Am i missing anything?

---

If your code isn't giving the same answer as @Kurt_Bremser gets, then you have changed something somehow and the only way we can help you further is if you show us the code you are using.

As with your other question, this can be done via PROC SUMMARY as well.

[bd_user_10]

Here is the dataset:

Obs	Date	SEDOL	PRCCD	BUY_SELL	VOLUME	PRICE	Buy_volume	Sell_volume
1	1996-01-22	4318130	70	.	.	.	.	.
2	1996-01-25	4318130	70	.	.	.	.	.
3	1996-01-26	4318130	73	.	.	.	.	.
4	1996-01-29	4318130	72	.	.	.	.	.
5	1996-01-30	4318130	73	10	200	73	200	.
6	1996-01-31	4318130	75.9	10	1900	75	1900	.
7	1996-01-31	4318130	75.9	20	5000	75	.	5000
8	1996-01-31	4318130	75.9	20	200	75.9	.	200
9	1996-02-01	4318130	80	20	10000	79	.	10000
10	1996-02-01	4318130	80	10	400	77	400	.
11	1996-02-01	4318130	80	10	5000	78	5000	.
12	1996-02-01	4318130	80	10	30000	79	30000	.
13	1996-02-01	4318130	80	10	900	77	900	.
14	1996-02-02	4318130	80	10	1600	80	1600	.
15	1996-02-05	4318130	85	20	10000	82	.	10000
16	1996-02-05	4318130	85	20	22500	82	.	22500
17	1996-02-06	4318130	85	.	.	.	.	.
18	1996-02-07	4318130	85	.	.	.	.	.
19	1996-02-09	4318130	84	.	.	.	.	.
20	1996-02-13	4318130	84	.	.	.	.	.
21	1996-02-14	4318130	84	.	.	.	.	.
22	1996-02-15	4318130	84	.	.	.	.	.
23	1996-02-16	4318130	84	.	.	.	.	.
24	1996-02-20	4318130	85	10	20000	85	20000	.
25	1996-02-21	4318130	83	10	10000	83	10000	.
26	1996-02-21	4318130	83	10	96000	83	96000	.
27	1996-02-22	4318130	85	10	5000	85.5	5000	.
28	1996-02-23	4318130	85.5	.	.	.	.	.
29	1996-02-28	4318130	86	.	.	.	.	.
30	1996-02-29	4318130	85	20	1000	85	.	1000
31	1996-02-29	4318130	85	20	1000	85	.	1000

This is the code I used

proc sql;

create table want_2 as

select

date,

sedol'

sum(Buy_volume) as Buy_volume,

sum(Sell_volume) as Sell_volume,

sum(case

when Buy_volume ne 0 then 1

else 0

end) as no_of_buy_order,

sum(case

when Sell_volume ne 0 then 1

else 0

end) as no_of_sell_order

from Have

group by date, sedol;

quit;

This is what I get

Obs	Date	SEDOL	Buy_volume	Sell_volume	no_of_buy_order	no_of_sell_order
1	1996-01-22	4318130	.	.	1	1
2	1996-01-25	4318130	.	.	1	1
3	1996-01-26	4318130	.	.	1	1
4	1996-01-29	4318130	.	.	1	1
5	1996-01-30	4318130	200	.	1	1
6	1996-01-31	4318130	1900	5200	3	3
7	1996-02-01	4318130	36300	10000	5	5
8	1996-02-02	4318130	1600	.	1	1
9	1996-02-05	4318130	.	32500	2	2
10	1996-02-06	4318130	.	.	1	1
11	1996-02-07	4318130	.	.	1	1
12	1996-02-09	4318130	.	.	1	1
13	1996-02-13	4318130	.	.	1	1
14	1996-02-14	4318130	.	.	1	1
15	1996-02-15	4318130	.	.	1	1
16	1996-02-16	4318130	.	.	1	1
17	1996-02-20	4318130	20000	.	1	1
18	1996-02-21	4318130	106000	.	2	2
19	1996-02-22	4318130	5000	.	1	1
20	1996-02-23	4318130	.	.	1	1
21	1996-02-28	4318130	.	.	1	1
22	1996-02-29	4318130	.	2000	2	2
23	1996-03-04	4318130	.	.	1	1
24	1996-03-05	4318130	.	.	1	1
25	1996-03-07	4318130	.	.	1	1
26	1996-03-13	4318130	.	.	1	1
27	1996-03-14	4318130	.	.	1	1
28	1996-03-15	4318130	.	.	1	1
29	1996-03-20	4318130	.	.	1	1
30	1996-03-25	4318130	.	.	1	1
31	1996-03-29	4318130	200	.	1	1

[PaigeMiller]

You have changed the data from your example. We (including @Kurt_Bremser) can only give you code that works on your example; we can't give you code that works on other data sets that you haven't shown us.

In particular, you now have missing values which your original data set did not have. So ... homework assignment ... modify Kurt's code to account for missings.

[bd_user_10]

Thank you for your hint. That worked!

[ballardw]

And another approach with a different procedure which is intended to work with missing values instead of 0 for the buy sell. Though the variable names have also apparently changed...

proc tabulate data=have;
   class date sedol;
   var buy_vol sell_vol;
   table date='' * sedol='',
         buy_vol='Buy Volume'*sum=''*f=best10.
         sell_vol='Sell Volume'*sum=''*f=best10.
         (buy_vol='Number buy orders' sell_vol='Number sell orders')*n=''
         /misstext='0'
   ;
run;

[bd_user_10]

Thanks for the code.