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.
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
"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;
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
@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.
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 |
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.
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: