Drop observations having null values

[PDevi]

Hello,

I have a table that has two variables, dt_sprt_doc_1 and dt_sprt_doc_2, with missing values for either one of them or both for an id. I would like to drop only those records that have the missing values for both.

The table is as below:

id     sprt_doc_1        dt_sprt_doc_1        sprt_doc_2         dt_sprt_doc_2

1       aaa                   22-AUG-2013         bbb                    29-AUG-2013

2       aaa                      .                           bbb                    05-MAY-2015

3       aaa                      .                           bbb                     .  

4       aaa                   18-DEC-2017         bbb                     .

I would like to drop id #3 because both the dt_sprt_doc_1 and dt_sprt_doc_2 have missing values for that id.

Can someone please help? Your help is appreciated.

[novinosrin]

If dt_sprt_doc_1 and dt_sprt_doc_2 are numeric then

if n(dt_sprt_doc_1 ,dt_sprt_doc_2) ne 0;

If char 

if coalescec(dt_sprt_doc_1, dt_sprt_doc_2) ne ' ';

/*applies to numeric as well*/

if coalesce(dt_sprt_doc_1, dt_sprt_doc_2) ne ' ';

[PeterClemmensen · Accepted solution]

data have;
input id sprt_doc_1 $ dt_sprt_doc_1 :date11. sprt_doc_2 $ dt_sprt_doc_2 :date11.;
datalines;
1 aaa 22-AUG-2013 bbb 29-AUG-2013
2 aaa . bbb 05-MAY-2015
3 aaa . bbb . 
4 aaa 18-DEC-2017 bbb .
;

data want;
   set have;
   if dt_sprt_doc_1=. & dt_sprt_doc_2=. then delete;
run;

[PDevi]

Thanks @PeterClemmensen. Appreciate your help.

[hashman]

@PDevi:

Just apply a proper WHERE clause to your input:

data have ;                                                                                                                                                                                                                                                     
  input id sprt_doc_1:$3. dt_sprt_doc_1:$11. sprt_doc_2:$3. dt_sprt_doc_2:date. ;                                                                                                                                                                               
datalines;                                                                                                                                                                                                                                                      
1  aaa  22-AUG-2013  bbb  29-AUG-2013                                                                                                                                                                                                                           
2  aaa  .            bbb  05-MAY-2015                                                                                                                                                                                                                           
3  aaa  .            bbb  .                                                                                                                                                                                                                                     
4  aaa  18-DEC-2017  bbb  .                                                                                                                                                                                                                                     
;                                                                                                                                                                                                                                                               
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
data want ;                                                                                                                                                                                                                                                     
  set have ;                                                                                                                                                                                                                                                    
  where cmiss (dt_sprt_doc_1, dt_sprt_doc_2) < 2 ;                                                                                                                                                                                                              
run ;                                   

Or:

proc sql ;                                                                                
  create table want as select * from have where cmiss (dt_sprt_doc_1, dt_sprt_doc_2) < 2 ;
quit ;                                                                                    

Note that the sample data set above is deliberately created with dt_sprt_doc_1 and dt_sprt_doc_2 of different data types to illustrate that the CMISS function (unlike NMISS) is data type insensitive.

Kind regards

Paul D. 

[PDevi]

Thanks @hashman. Worked beautifully and I learned something very new; didn't know about the CMISS function.