Solved: update status if one meets the criteria in a group

Yvegunta · Posted 11-17-2016 07:06 PM

Hi Everyone

I have created this table, however what i need is the "Status" to be "Not Verified" for all, if one of them is "Not Verified" for the same name.

Current Table:

What i am ideally looking for is:

Thank you

Shmuel · Posted 11-17-2016 08:55 PM

proc sql;

create table want as

select name, rule,

case when (name in (select name from have where rule='not verified'))

then 'not verified' else 'verified' end as status

from have;

quit;

Shmuel · Posted 11-17-2016 08:55 PM

proc sql;

create table want as

select name, rule,

case when (name in (select name from have where rule='not verified'))

then 'not verified' else 'verified' end as status

from have;

quit;

Yvegunta · Posted 11-17-2016 09:33 PM

Thank you Shmuel, worked exactly as I wanted it.

mkeintz · Posted 11-17-2016 09:02 PM

If your data are sorted by name then it's straighforward:

data want;
  merge have
        have (keep=name status where=(status='not Verified'));
  by name;
run;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

update status if one meets the criteria in a group