replace missing values with a maximum number of consecutive replacements

[JoeyE]

Hi there,

I am quite new to using SAS and I have encoutnered a problem that I cant find any solution to online so far.
I have three variables: 1) Customer 2) Month 3) Rating

My dataset (names testprogram1) thus looks as follows:

Customer       Month               Rating

1                      Jan                     10

1                      Feb                     11

1                      March                    .

1                      April                       .

1                      May                       . 

1                      June                      .

1                      July                       .

1                        ....                        .

1                       Dec                      .

2                       Jan                     15

2                      Feb                      16

2                      March                  16.

2                      April                       .

2                      May                      16 

2                     .... 

2                       Dec                      .

I need to ne make 2 corrections to that dataset:

1) I need toreplace any missing rating (for the same cusomter) with the previous rating. HOWEVER, I must only replace the next 4 missing values with the previous rating. Hence, m y output would need to look like this (for customer 1):

Customer       Month               Rating

1                      Jan                     10

1                      Feb                     11

1                      March                    11

1                      April                       11

1                      May                       11

1                      June                      11

1                      July                       .

1                        ....                        .

1                       Dec                      .

 I have worked out the following code BUT I cant manage to make him only replace a maximum of 4 consecutive missing ratings:
data testprogramm 2;

set testprogramm1;

by customer;

if first.customer or rating > . then fill=rating;

rename fill=rating2;

retain fill;

run;

2) If a customer has a rating of 16, I need to delete all ratings that follow for that cusomter (a customer can only have a rating of 16 once. after that no more ratings can be matched to the customer.

If anyone could help me with these or at least of the problems I would be very very grateful.

cheers
Joey

[art297 · Accepted solution]

I think that the following satisfies all of your conditions:

data want (drop=last_rating missing_counter got16);
  set have;
  by customer;
  retain last_rating missing_counter got16;
  if first.customer then do;
    call missing(last_rating);
    missing_counter=0;
    got16=0;
  end;
  if got16 then call missing(Rating);
  else if Rating eq 16 then got16=1;
  else if not missing(Rating) then do;
    last_rating=rating;
    missing_counter=0;
  end;
  else do;
    missing_counter+1;
    if missing_counter le 4 then Rating=last_rating;
  end;
run;

Art, CEO, AnalystFinder.com

[JoeyE]

Dear Art,

Thank you very much.
As far as I can see, it is working perfectly.
Have a great weekend .

Cheers