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:
Name | rule | status |
abc | not verified | not verified |
abc | verified | verified |
abc | verified | verified |
abc | verified | verified |
What i am ideally looking for is:
Name | rule | status |
abc | not verified | not verified |
abc | verified | not verified |
abc | verified | not verified |
abc | verified | not verified |
Thank you
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;
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;
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;
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.
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.