Replace missing characters with last non missing character multiple id's.

[Paakay]

Hello All,

I am writing a code to replace all missing charcters after the last non missing character with the last non missing character by member id but my code replaces all member ids with the first member id's last non missing character . Each member id is in the data 16 times. Can someone help me on how the code should break once it encounters a new member id and use the new member id's last non missing character to populate all missing characters after the last non-missing character ? I tried the code below by doesn't work. I want to replace the missing characters after the last non missing character with the last non missing character. For example for id 1, missing character after 2019 quarter 1 will have 'abc'. For id 2, missing character after 2018, 4th quarter will have 'ghi' .Snippet of my code is below but it doesn't work.. Your help is appreciated

Data want;
input ID FY Qtr Missing_Char $3.
datalines;
1 2018 1 ''
1 2018 2  ''
1 2018 3 ''
1 2018 4 ''
1 2019 1 abc
1 2019 2 ''
1 2019 3 ''
2 2018 1 abc
2 2018 2 ''
2 2018 3 def
2 2018 4 ghi
2 2019 1 ''
2 2019 2 ''
2 2019 3 ''
3 2019 4 ''
;
run

Data chk;
 set want;
by id ;
retain missing_char;
 if not missing(missing_char) then _missing_char=missing_char;
else missing_char=_missing_char;
_drop missing_char;
run;

[ChrisNZ]

Well first you need to identify the last quarter with a value for each ID.

data LAST;
  set HAVE;
  where CHAR ne '..';
  by ID YEAR QUARTER;
  if last.ID;
 run;

Then you use that information

data WANT;
   length RETAIN $3;
   retain RETAIN;
   merge HAVE LAST(in=LASTVAL);
   by ID YEAR QUARTER;
   if first.ID then RETAIN='   ';
   if LASTVAL then RETAIN=CHAR ;
   else if RETAIN ne ' ' then CHAR=RETAIN;
 run;