topic Re: update column value in existing table with condition in SAS Programming

update column value in existing table with condition

leonzheng — Sun, 18 Mar 2018 00:50:13 GMT

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

Re: update column value in existing table with condition

Astounding — Sun, 18 Mar 2018 01:01:30 GMT

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.

Re: update column value in existing table with condition

leonzheng — Sun, 18 Mar 2018 01:08:02 GMT

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

Re: update column value in existing table with condition

Astounding — Sun, 18 Mar 2018 07:09:26 GMT

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;

Re: update column value in existing table with condition

novinosrin — Sun, 18 Mar 2018 03:17:47 GMT

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;

Re: update column value in existing table with condition

leonzheng — Sun, 18 Mar 2018 04:22:41 GMT

thank you