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
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.