DATA Step, Macro, Functions and more

replace missing values with a maximum number of consecutive replacements

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

replace missing values with a maximum number of consecutive replacements

[ Edited ]

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


Accepted Solutions
Solution
‎06-09-2017 11:07 AM
PROC Star
Posts: 7,366

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

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

 

View solution in original post


All Replies
Solution
‎06-09-2017 11:07 AM
PROC Star
Posts: 7,366

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

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

 

New Contributor
Posts: 4

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

Dear Art,

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

Cheers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 95 views
  • 0 likes
  • 2 in conversation