how to derive only n flag records

[thanikondharish]

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

[PeterClemmensen]

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;

[hashman]

@thanikondharish:

@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.

[tsap]

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.

[hashman]

@tsap:

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. 

[tsap]

@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

[hashman]

@tsap : Yup, that is it.