topic Re: how to derive only n flag records in SAS Programming

how to derive only n flag records

thanikondharish — Sat, 22 Jun 2019 06:32:02 GMT

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

Re: how to derive only n flag records

PeterClemmensen — Sat, 22 Jun 2019 06:43:17 GMT

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;

Re: how to derive only n flag records

hashman — Sat, 22 Jun 2019 18:49:52 GMT

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

Re: how to derive only n flag records

tsap — Tue, 25 Jun 2019 12:30:44 GMT

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.

Re: how to derive only n flag records

hashman — Tue, 25 Jun 2019 15:51:18 GMT

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

Re: how to derive only n flag records

tsap — Tue, 25 Jun 2019 16:00:47 GMT

@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

Re: how to derive only n flag records

hashman — Tue, 25 Jun 2019 16:03:31 GMT

@tsap : Yup, that is it.