what a champ! ... View more

Hi Ballardw, I should have just said month1 month2 not lag1 and lag2. Silly me! ... View more

These codes works fine! ... View more

Basically, this is what I need:   sum TNUM, TVOL and DVOL for the following period   if relday -60 to -30 then month is -2 if relday -30 to -1 is month is -1 if relday 0 then month is 0 if relday 1 to 30 then month is 1 if relday 30 to 60 then month is 2   I have many sedol, and I want to do the same for all. I hope this make sense. ... View more

Thanks for the code. ... View more

Thank you for your hint. That worked! ... View more

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 ... View more

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? ... View more