topic Re: How to retrieve the result for missing value by capturing the data in the next available observa in SAS Enterprise Guide

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

y3arire_7 — Thu, 18 Apr 2019 09:08:03 GMT

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

Re: How to retrieve the result for missing value by capturing the data in the next available observa

FredrikE — Thu, 18 Apr 2019 11:23:57 GMT

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

Re: How to retrieve the result for missing value by capturing the data in the next available observa

TomKari — Thu, 18 Apr 2019 20:25:20 GMT

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;

Re: How to retrieve the result for missing value by capturing the data in the next available observa

y3arire_7 — Fri, 19 Apr 2019 02:34:24 GMT

Tested! It works! 😉