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 |
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
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
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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.