DATA Step, Macro, Functions and more

update column value in existing table with condition

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

update column value in existing table with condition

I want to update value of one column in an existing table by some condition. Please help, table 'test' looks like below:

ID    Group1    Value1    Group2    Value2    Comment

01     aaa            1            bbb           2

02     aaa            2            bbb           1

03     aaa            3            bbb           4

04     cc              2             d              6

05     cc              1             d              4

06     cc              5             e              7

 

The idea is add value to Comment, compare Value1 or Value2 by corresponding group, the row with the lowest value will have 'Y' in comment, else get a 'N'. For example, row ID=01 has value1=1 which is lowest in Group1=aaa, then comment='Y'; row ID=02 has value1=2 not the lowest in aaa, but it has Value2=1, the lowest in Group2=bbb, so comment='Y'.

The updated table 'want' would be like below:

ID     Group1      Value1      Group2     Value2       Comment

01       aaa            1               bbb            2                 Y

02       aaa            2               bbb            1                 Y

03       aaa            3               bbb            4                 N

04       cc              2                 d              6                 N

05       cc              1                 d              4                 Y

06       cc              5                 e              7                 Y

 

How can I do it in SAS?

 

Thanks,

Leon


Accepted Solutions
Solution
‎03-18-2018 12:22 AM
Super User
Posts: 6,935

Re: update column value in existing table with condition

Posted in reply to leonzheng

The request seems a little unusual, because the groupings in Group 1 are different than the groupings in Group 2.  But assuming you want what you described, it will take two passes through the data as a result:

 

data temp;

do until (last.group1);

   set have;

   by group1 notsorted;

   minval = min(minval, value1);

end;

do until (last.group1);

   set have;

   by group1 notsorted;

   if value1=minval then comment='Y';

   else comment = 'N';

   output;

end;

drop minval;

run;

 

That completes part of the task, assigning COMMENT based on GROUP1 and VALUE1.  Next, change the value of COMMENT based on the GROUP2 and VALUE2 groupings:

 

data want;

do until (last.group2);

   set have;

   by group2 notsorted;

   minval = min(minval, value2);

end;

do until (last.group2);

   set have;

   by group2 notsorted;

   if value2=minval then comment='Y';

   output;

end;

drop minval;

run;

 

All of this assumes that your data is already grouped ... all identical GROUP1 values appear in a sequence, and all identical GROUP2 values appear in a sequence.  If that's not the case, some sorting may also be needed.

 

It's intentional that the word ELSE appears in the top DATA step but not in the bottom one.

View solution in original post


All Replies
Solution
‎03-18-2018 12:22 AM
Super User
Posts: 6,935

Re: update column value in existing table with condition

Posted in reply to leonzheng

The request seems a little unusual, because the groupings in Group 1 are different than the groupings in Group 2.  But assuming you want what you described, it will take two passes through the data as a result:

 

data temp;

do until (last.group1);

   set have;

   by group1 notsorted;

   minval = min(minval, value1);

end;

do until (last.group1);

   set have;

   by group1 notsorted;

   if value1=minval then comment='Y';

   else comment = 'N';

   output;

end;

drop minval;

run;

 

That completes part of the task, assigning COMMENT based on GROUP1 and VALUE1.  Next, change the value of COMMENT based on the GROUP2 and VALUE2 groupings:

 

data want;

do until (last.group2);

   set have;

   by group2 notsorted;

   minval = min(minval, value2);

end;

do until (last.group2);

   set have;

   by group2 notsorted;

   if value2=minval then comment='Y';

   output;

end;

drop minval;

run;

 

All of this assumes that your data is already grouped ... all identical GROUP1 values appear in a sequence, and all identical GROUP2 values appear in a sequence.  If that's not the case, some sorting may also be needed.

 

It's intentional that the word ELSE appears in the top DATA step but not in the bottom one.

Contributor
Posts: 48

Re: update column value in existing table with condition

Posted in reply to Astounding
Thank you Astounding for the quick reply. I guess my data is not sorted yet. So how should I sort Group1 and Group2 since they cannot be both perfectly sorted
Super User
Posts: 6,935

Re: update column value in existing table with condition

[ Edited ]
Posted in reply to leonzheng

Sort by GROUP1 before the first DATA step.  Then sort by GROUP2 before the second DATA step.

 

As long as you are going to sort the data set twice, you might as well take advantage of that and simplify the programming.  At least I think this looks simpler.  For example:

 

proc sort data=have;

by group1 value1;

run;

data temp;

set have;

by group1;

if first.group1 then minval = value1;

retain minval;

if value1=minval then comment='Y';

else comment='N';

drop minval;

run;

 

proc sort data=temp;

by group2 value2;

run;

 

data want;

set temp;

by group2;

if first.group2 then minval = value2;

if value2=minval then comment='Y';

drop minval;

run;

Super User
Posts: 2,068

Re: update column value in existing table with condition

Posted in reply to Astounding
data have;
input ID $    Group1 $   Value1    Group2 $   Value2 ;
datalines;
01     aaa            1            bbb           2
02     aaa            2            bbb           1
03     aaa            3            bbb           4
04     cc              2             d              6
05     cc              1             d              4
06     cc              5             e              7
;

proc sql;
create table want as
select *, case when min(value1)=value1 or min(value2)=value2 then 'Y' else 'N' end as Comment
from have
group by  Group1,Group2
order by id;
quit;
Contributor
Posts: 48

Re: update column value in existing table with condition

Posted in reply to novinosrin
thank you
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 233 views
  • 1 like
  • 3 in conversation