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
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.
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.