Keeping obsv. 1mnth before a specific date

[Ksharp]

If your date variable is NUMERIC type. You need firstly convert it into DATE type.Like:

new_date=input(put(date,best10.),yymmdd10.) ;

After that , You can run my code.

You see My log has not any ERROR message .

Ksharp

[mkeintz]

1. You have data sorted by permno and date.

2. The data is monthly (but possibly with some missing months).  This is the only reason you have to compare the dates in consecutive records.

3. You've reported that the date variable (unfortunately) is not a sas date value, so you have to modify the date values before using the INTCK or INTNX function.

  soapbox:  why don't you keep SAS date values, in addition to your numeric values if you must.  It's no harder to specify date ranges  (ie..  where date between '01jan2001'd and '31dec2002'd) vs what I presuem you would do:  where datemonth between 200101 and 200212), and it has the great advantage of measuring intervales in terms of days  ( days=date2-date1), weeks, months, qtrs, years, etc.).  soapbox off.

4. You want to look ahead one record for a particular dist code.

You can use the FIRSTOBS=-2 option in a second set statement to look ahead and check for desired DISTCD.  Then you know whether to output the record-in-hand.

Using the DATA step below compares only consecutive records, so it should be much faster than the proc sql approach which needs to compare every possible record pair to decide whether to keep a particular set of records.

Also the DATA step can create two (or more) data sets in one stepm while the CREATE TABLE statement (to the best of my knowledge) can create only one table per statement. This code creates two data sets: REOCRD_PAIRS (the record preceding the 1262,1272 and the record with the 1262,1272) and RETURNS (the record with the 1262, 1272 with a new variables: return).

BTW the negative PRC value is CRSP's way of signifying that no trading took place during the month, so the value presented is the negative of the mid-point of the best bid and best ask.  So you might choose to use the absolute value of PRC.  I don't know off hand what DISTCD of 1262, 1272 mean, but if they are something like distributions from going bankrupt, I could see why they might have been already delisted or not traded on public stock exchanges.

data record_pairs (keep=date permno distcd divamt prc monthdate)

        distcd_returns (keep=date permno distcd divamt prc monthdate return);

  set have  end=end_of_have;  ** end_of_have=1 if current record is the last **;
  by permno date;                     ** Establishes first.permno, last.permno, etc. dummy variables **;

  /* If we are not at end of data, then read ahead on record */

  if end_of_have=0 then

     set have (firstobs=2 keep=distcd rename=(distcd=next_distcd));

  else next_distcd=.;

  ** First deal with outputing observations to RECORD_PAIRS **;

  ** If the next record is a 1262, 1272 and record-in-hand is not end of permno, or

     if current record is a 1262, 1272 and is not start of a permno then   output it **;

  if (next_distcd in (1262,1272) and last.permno=0)  or

     (distcd in(1262,1272) and first.permno=0)  then output record_pairs;

  ** Now deal with populating the returns data set **;

  lag_prc=abs(lag(prc));

  lag_date=lag(date);

  if distcd in (1262,1272) and first.permno=0 then do;   

     return=div_amt/lag_prc;

     if return=. then return_grp=.;

     else if return<= .05 then return_grp=1;

     else if return< .1 then return_grp=2;

     else return_grp=3;

     ** Finally if this record is 1 month after prior record, output it **;

     if  intck('month',input(lag_date,yymmdd8.),input(date,yymmddn8.))=1 then output returns;

   end;

run;