topic Re: Dynamically replace missing character after last non missing character for each id in SAS Programming

Dynamically replace missing character after last non missing character for each id

Paakay — Thu, 23 Dec 2021 12:13:51 GMT

Hi,

I want to dynamically replace all missing characters after the last non missing character with the last non missing character for each member id.Example member_id 1 in FY 2018 , Qtr 4 , since comment is missing, it should be replaced with the last non missing comment which is 'abc'. For member_id 2, sine comment is missing for FY 2018, Qtrs 3 and 4, they should be replaced by 'def' . For member_id 3, since comment is missing for FY2018, Qtrs 2,3 and4, they would be replaced with 'ghi' Could some help me out. Sample data is below

Data have;
input member_id FY Qtr Comment $12.;
datalines;
1 2018 1 
1 2018 2 
1 2018 3 'abc'
1 2018 4 
2 2018 1
2 2018 2 'def'
2 2018 3 
2 2018 4
3 2018 1 'ghi'
3 2018 2 
3 2018 3
3 2018 4
;
run;

This is what I want.

Data want;
input member_id FY Qtr Comment $12.;
datalines;
1 2018 1 
1 2018 2 
1 2018 3 'abc'
1 2018 4 'abc'
2 2018 1
2 2018 2 'def'
2 2018 3 'def'
2 2018 4 'def'
3 2018 1 'ghi'
3 2018 2 'ghi'
3 2018 3 'ghi'
3 2018 4 'ghi'
;
run;

Re: Dynamically replace missing character after last non missing character for each id

PaigeMiller — Thu, 23 Dec 2021 12:58:28 GMT

This is a job for the RETAIN statement! If there was an official emoji for the RETAIN statement, I would insert that emoji here.

data want;
    set have;
    retain comment1;
    by member_id;
    if first.member_id then comment1=comment;
    if not missing(comment) then comment1=comment;
run;

Re: Dynamically replace missing character after last non missing character for each id

r_behata — Thu, 23 Dec 2021 16:23:55 GMT


data want;
	update have(obs=0) have;
	by  member_id FY;
	output;
run;