data ex ;
input id $ name $ flag $ ;
cards ;
101 surya y
101 surya y
101 surya n
102 bulli y
103 dhanush n
103 dhanush n
104 sai n
;
i have one data set like above . How to derive n flag for example
id should have only n value shouldn't be other values like see below example
1)103 dhanush n
2)104 sai n
103,104 have only n value
Here is one way
data want;
if _N_ = 1 then do;
declare hash h(dataset:"ex(where=(flag='y'))");
h.defineKey('id');
h.defineDone();
end;
set ex;
if h.check() ne 0;
run;
@PeterClemmensen 's solution is best since it doesn't depend on the record order and will work whether the input data set is unsorted or sorted. If it is sorted, though (as in your sample), the double DoW-loop is the usual vehicle to get where you need:
data ex ;
input id $ name $ flag $ ;
cards ;
101 surya y
101 surya y
101 surya n
102 bulli y
103 dhanush n
103 dhanush n
104 sai n
run ;
data need (drop = _:) ;
do _n_ = 1 by 1 until (last.id) ;
set ex ;
by id ;
if flag = "y" then _exclude = 1 ;
end ;
do _n_ = 1 to _n_ ;
set ex ;
if not _exclude then output ;
end ;
run ;
Kind regards
Paul D.
If the only expectation for output, is that all values in the flag field are set to 'n', then the logic could be as simple as this:
DATA WORK.WANT;
SET WORK.HAVE;
Flag='n';
RUN;
Or if you would prefer not to overwrite the flag field you can use this logic:
DATA WORK.WANT (RENAME=(Flag2=Flag));
SET WORK.HAVE;
FLAG2='n';
DROP FLAG;
RUN;
If you want to use conditional logic to review the current values of flag and update only the ones containing 'y' to 'n', then you can definitely go with the alternative solutions provided by the other users.
If it were the expectation, you'd be right. But it's not: The expectation is to output each group of records with the same ID where all the values of flag="n"; or, in other words, exclude any ID-group where at least one record has flag not equal to "n".
Kind regards
Paul D.
@hashman : Oh, ok. I definitely did not understand that was the ask from the original post. Thanks for clearing that up.
@thanikondharish : Now that I understand what you are looking for, here is a third option to accomplish the output you need:
PROC SQL;
CREATE TABLE WORK.WANT AS
SELECT DISTINCT
ID
, Name
FROM WORK.HAVE
GROUP BY ID, Name
HAVING SUM(CASE WHEN Flag='y' then 1 else 0 end)=0;
QUIT;
Results:
id name
103 dhanush
104 sai
@tsap : Yup, that is it.
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.