topic replace missing values with a maximum number of consecutive replacements in SAS Programming

replace missing values with a maximum number of consecutive replacements

JoeyE — Fri, 09 Jun 2017 09:49:38 GMT

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

Re: replace missing values with a maximum number of consecutive replacements

art297 — Fri, 09 Jun 2017 14:29:37 GMT

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

Re: replace missing values with a maximum number of consecutive replacements

JoeyE — Fri, 09 Jun 2017 15:08:04 GMT

Dear Art,

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

Cheers