How to retrieve the result for missing value by capturing the data in the next available observation

[y3arire_7]

Hi Is anyone here could share how to write the code to reflect the "Desired Results" as below?

I would like to have the blank field from "Membership_Type" to be replaced by number from the next following month with available data.

Truly appreciate if anyone could shed the light on this question.

Thank you so much!

YYYYMM	Customer	Membership_Type	Desired Results
201712	1	1100	1100
201801	1	1100	1100
201802	1	1100	1100
201803	1	1100	1100
201804	1	.	2200
201805	1	.	2200
201806	1	.	2200
201807	1	.	2200
201808	1	.	2200
201809	1	.	2200
201810	1	.	2200
201811	1	2200	2200
201812	1	2200	2200
201901	1	2200	2200
201712	3	.	3000
201801	3	.	3000
201802	3	.	3000
201803	3	.	3000
201804	3	.	3000
201805	3	.	3000
201806	3	3000	3000
201807	3	3000	3000
201808	3	3000	3000
201809	3	3000	3000
201810	3	3000	3000
201811	3	3000	3000
201812	3	3000	3000

[FredrikE · Accepted solution]

HI!

Something like (untested):

proc sort data = have out=tmp;

 by customer descending yyyymm;

run;

data want;

 set tmp;

 by customer descending yyyymm;

 retain desired_result;

 if not missing(membership_type) then desired_restult = membership_type;

run;

//Fredrik

[y3arire_7]

Tested! It works! 😉

[TomKari]

Here's a very similar version, but it will keep the result dataset in the original sequence, whether or not there's a sortable key.

Tom

data Inter01;
	set Have;
	_SN = _n_;
run;

proc sort data=Inter01;
	by descending _SN;
run;

data Inter02;
	retain old_membership_type;
	set Inter01;

	if _n_ = 1 then
		old_membership_type = membership_type;

	if missing(membership_type) then
		membership_type = old_membership_type;
	old_membership_type = membership_type;
	drop old_membership_type;
run;

proc sort data=Inter02 out=Want(drop=_SN);
	by _SN;
run;