summing certain observations

Reply
Occasional Contributor
Posts: 15

summing certain observations

Hi everyone,

I have the following dataset

data crsp_test;

  infile datalines delimiter=',';

  input cusip date price insider;

  datalines;

  222, 01011985, 5.15, 1

  222, 02011985, 4.14

  222, 03011985, 6.16

  222, 04011985, 20.00, 1

  222, 05011985, 2.41

  222, 06011985, 3.24

  222, 07011985, 5.25, 1

  222, 08011985, 2.56

  222, 09011985, 2.25

  222, 10011985, 5.60

  333, 01011985, 3.52, 1

  333, 02011985, 4.14, 1

  333, 03011985, 7.23

  333, 04011985, 1.45

  333, 05011985, 5.63

  333, 06011985, 5.13

  333, 07011985, 5.25

  333, 08011985, 5.12

  333, 09011985, 2.34

  333, 10011985, 7.65

  444, 01011985, 4.15

  444, 02011985, 4.30

;

run;

What I would like to do is everytime the final column = 1 then I would like to sum the price for the next three observationss

For example from the first line the final column is =1 I would like to sum (4.14+6.16+20.00) and cal this variable total

It needs to be done on a company by company basis (company id is the first column).

I tried the following code but haven't had any success

data sum_total;

     set crsp_test;

     if insider =1 then sum(price+3);

run;

The second column is dates if that helps.


Am very new to SAS so any help is greatly appreciated.


Cheers

Super Contributor
Posts: 644

Re: summing certain observations

Posted in reply to wschnell1

What is your business rule in the case of company 333 where two consecutive records are flagged as 1?  Do you want 2 sums of each 3 rows, or only the second sum, or something else?

You can import dates as SAS dates (I'm guessing for N America) as

  format date MMDDYY10. ;

  input cusip date MMDDYY10. price insider;

Richard in NZ

Ask a Question
Discussion stats
  • 1 reply
  • 160 views
  • 0 likes
  • 2 in conversation