replacing value in a column with a value in the same column

[Teja5b8]

I am trying to automate a manual task which I do with Filters in Excel.

Data A;

Input ID ENDDT NM1 $ NM2 $;

datalines;

123 2/28/2018 Roxy Yen

124 3/23/2018 Roxy Yen

125 1/3/3018 Max Nancy

126 2/20/2016 Yen Ungrouped

127 3/4/2014 Yen Ungrouped

128 1/12/2012 Nancy Ungrouped

129 2/26/2016 Jeff Steve

130 3/22/2017 Max Nancy

;

Step 1 - When there is ungrouped in Nm2, it should take NM1 of that row and check if the same name exists in NM2 in some other row, then Ungrouped should be replaced with that value(Ex - For row 126 Ungrouped should be replaced with Yen from either 123 or 124)

Step 2 - After NM2 replacement, NM1 of that row must be replaced with NM1 from which previous NM2 replacement happened(Ex- For row 126 Yen should be replaced by Roxy)

Similarly For ID 128, Ungrouped should be replaced with Nancy by checking either rows 125 or 130 and Nancy in NM1 should be replaced with Max.

Finally there should not be any ungrouped values in NM2, if there is no match found from previous rows, it should print a message with all ID's.

The dataset should look like this in the end

123 2/28/2018 Roxy Yen

124 3/23/2018 Roxy Yen

125 1/3/3018 Max Nancy

126 2/20/2016 Roxy Yen

127 3/4/2014 Roxy Yen

128 1/12/2012 Max Nancy

129 2/26/2016 Jeff Steve

130 3/22/2017 Max Nancy

Please let me know, how can I do this using general SAS programming. Any help would be highly appreciated.

[ChrisNZ · Accepted solution]

Without knowing much about your data, this works:

proc sql;
   select a.ID
         ,a.ENDDT
         ,coalesce(b.NM1,a.NM1)  as NM1
         ,coalesce(b.NM2,a.NM2)  as NM2
   from HAVE        a
        left join
        (select unique NM1, NM2 from HAVE where NM2 ne 'Ungrouped') b 
        on  a.NM2 = 'Ungrouped'
        and a.NM1 = b.NM2
   order by ID ;
quit;

ID	ENDDT	NM1	NM2
123	28-Feb-18	Roxy	Yen
124	23-Mar-18	Roxy	Yen
125	3-Jan-18	Max	Nancy
126	20-Feb-16	Roxy	Yen
127	4-Mar-14	Roxy	Yen
128	12-Jan-12	Max	Nancy
129	26-Feb-16	Jeff	Steve
130	22-Mar-17	Max	Nancy

[Teja5b8]

Thanks Chris. Appreciate your support.