topic Re: if you have NA in all character variables then you should delete that record in SAS Programming

if you have NA in all character variables then you should delete that record

Saikiran_Mamidi — Sun, 22 Dec 2019 06:45:08 GMT

data abc;
input col1 $ col2 col3 col4 $ Col5 $;
datalines;
NA 10 20 NA Hi
NA 11 22 NA NA
NA 12 13 NA ho
NA 16 22 BA jj
10 20 17 NA NA

;

run;

so only second row satisfies...

refrain from hard coding...

no hard coding please i.e. mention of columns. this is simple example, what if you had 100 columns;;

Re: if you have NA in all character variables then you should delete that record

Kurt_Bremser — Sun, 22 Dec 2019 07:25:24 GMT

Define an array over _character_ and iterate through it to set a flag variable; if flag is set at the end of the loop, keep the observation.

Re: if you have NA in all character variables then you should delete that record

Saikiran_Mamidi — Sun, 22 Dec 2019 10:26:23 GMT

can i get the code if possible.many thanks

Re: if you have NA in all character variables then you should delete that record

VDD — Sun, 22 Dec 2019 13:12:13 GMT

what have you tried? everyone wants code to be provided for them without learning......

Coding is more than just taking someone else's code and using it in you requirements.

Re: if you have NA in all character variables then you should delete that record

Kurt_Bremser — Sun, 22 Dec 2019 13:42:47 GMT

@Saikiran_Mamidi wrote:
can i get the code if possible.many thanks

Learn by doing, and reading the documentation:

ARRAY Statement

DO Statement: Iterative

IF Statement: Subsetting

Re: if you have NA in all character variables then you should delete that record

ed_sas_member — Sun, 22 Dec 2019 15:41:13 GMT

Hi @Saikiran_Mamidi

You can try this:

- the array '_char' contains all character variables.

- a counter is initiated (count = 0);

- each time a variable of the array is equal to "NA", 1 is added to the counter

-> so the counter, at the end, contains the number of character variable equal to 'NA'.

Then , the value of this counter is compared to the number of variables in the array (= the dimension of the array represented by dim(array), which is equal to 3 in your example)

data want;
	set abc;
	array _char(*) _character_;
	count = 0;
	do i=1 to dim(_char);
		if _char(i) = "NA" then count+1;
	end;
	if count = dim(_char) then delete;
	drop i count;
run;

Re: if you have NA in all character variables then you should delete that record

Tom — Sun, 22 Dec 2019 22:20:44 GMT

Why do the variables have NA in them to begin with? What if the variable has a valid value of NA? Perhaps it is storing the abbreviation for sodium. Or it has a person's last name.

Re: if you have NA in all character variables then you should delete that record

hashman — Tue, 24 Dec 2019 16:10:40 GMT

@Tom: NA = Not Applicable, I'd guess.

Re: if you have NA in all character variables then you should delete that record

hashman — Tue, 24 Dec 2019 17:58:09 GMT

@Saikiran_Mamidi:

This should do it:

data have ;                                                                                                                             
  input (col1-col5) ($) ;                                                                                                               
  cards ;                                                                                                                               
NA 10 20 NA Hi                                                                                                                          
NA 11 22 NA NA                                                                                                                          
NA NA NA NA NA                                                                                                                          
NA 12 13 NA ho                                                                                                                          
NA 16 22 BA jj                                                                                                                          
10 20 17 NA NA                                                                                                                          
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data want ;                                                                                                                             
  set have ;                                                                                                                            
  array col col: ;                                                                                                                      
  if count (cats (of col:), "NA") < dim (col) ;                                                                                         
run ;

Bear in mind, however, that if combined length of the COL variables should exceed 32767, there would be not enough CAT buffer length to accommodate the internal concatenation. That's why an array approach, though less elegant and more verbose, is more robust:

data want (drop = _:) ;                                                                                                                 
  set have ;                                                                                                                            
  retain _stop "" ;                                                                                                                     
  array col[*] col: _stop ;                                                                                                             
  do _i = 1 by 1 until (col[_i] ne "NA") ;                                                                                              
  end ;                                                                                                                                 
  if _i < dim (col) - 1 ;                                                                                                               
run ;

Note how the sentinel _STOP is made use of to save one key comparison in each iteration of the loop - the trick termed by D. Knuth "quick sequential search".

Kind regards

Paul D.