Recognize when a new cusip starts.

[mexes]

Hi everybody

How can I tell SAS to start over and over the same calculation steps when a variable, called cusip, beginns with a new cusip? Do I have to do this with a do-loop?

My data looks like this. I have the date, cusip and the price of a stock and I want to calculate the return. The mistake here is that SAS takes from the last cusip the stock price and does the return calculation with the stock price of the new cusip but this is not correct, since the cusip stands for onw company and the cusip is a unique identifier. SAS should generate always a . (dot) for the first observation when a new cusip starts. The cusip's should be independent of each other. I do this for about 400 firms with 400 different cusip's. The cusip does not start always at 19891229 (date), so the function should be contingent upon the cusip and not the date. Is there a function such that SAS recognizes that a new cusip starts?

ret=(prc(t)/prc(t-1))-1

date               cusip               prc                 ret   

19891229      00036110       36.00000       .

19900330      00036110       30.00000     -0.1667

19900629      00036110       24.00000     -0.2000

19900928      00036110       12.25000     -0.4896

19891229      00103110       14.87500     0.2142

19900330      00103110       14.62500     -0.0168

19900629      00103110       14.25000     -0.0256

19900928      00103110       7.12500       -0.5773

The output should look like this: Always when a new cusip starts there should be a . (dot) on the right side under the ret.

date               cusip               prc                 ret   

19891229      00036110       36.00000       .

19900330      00036110       30.00000     -0.1667

19900629      00036110       24.00000     -0.2000

19900928      00036110       12.25000     -0.4896

19891229      00103110       14.87500     .

19900330      00103110       14.62500     -0.0168

19900629      00103110       14.25000     -0.0256

19900928      00103110       7.12500       -0.5773

Thanks for your help!!

[ballardw · Accepted solution]

data want;

     set have;

     by cusip;

     if first.cusip then ret=.;

     else ret=(prc(t)/prc(t-1))-1 ;

run;

[Reeza]

mexes wrote:


Is there a function such that SAS recognizes that a new cusip starts?

Add a BY statement to your data processing and then use something like:

Your data does need to be sorted appropriately.

BY cuspid;

*rest of your code;

if first.cuspid then ret=.;

[mexes]

Thanks for the help...now everything works fine.