BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
leonzheng
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

5 REPLIES 5
Astounding
PROC Star

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.

leonzheng
Obsidian | Level 7
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
Astounding
PROC Star

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;

novinosrin
Tourmaline | Level 20
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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