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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

[ Edited ]

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.


Accepted Solutions
Solution
‎04-15-2018 07:34 PM
PROC Star
Posts: 2,360

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

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

 

 

View solution in original post


All Replies
Solution
‎04-15-2018 07:34 PM
PROC Star
Posts: 2,360

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

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

 

 

New Contributor
Posts: 2

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

Thanks Chris. Appreciate your support.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 108 views
  • 0 likes
  • 2 in conversation