DATA Step, Macro, Functions and more

update status if one meets the criteria in a group

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

update status if one meets the criteria in a group

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:

Namerulestatus
abcnot verifiednot verified
abcverifiedverified
abcverifiedverified
abcverifiedverified

 

What i am ideally looking for is:

Namerulestatus
abcnot verifiednot verified
abcverifiednot verified
abcverifiednot verified
abcverifiednot verified

 

 

Thank you


Accepted Solutions
Solution
‎11-17-2016 09:32 PM
Trusted Advisor
Posts: 1,374

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

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;

View solution in original post


All Replies
Solution
‎11-17-2016 09:32 PM
Trusted Advisor
Posts: 1,374

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

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;

Occasional Contributor
Posts: 13

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

Thank you Shmuel, worked exactly as I wanted it.
Valued Guide
Posts: 797

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

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;
☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 225 views
  • 0 likes
  • 3 in conversation