BookmarkSubscribeRSS Feed
Paakay
Calcite | Level 5

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;
2 REPLIES 2
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller
r_behata
Barite | Level 11

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 747 views
  • 5 likes
  • 3 in conversation