topic Re: Drop observations having null values in SAS Programming

Drop observations having null values

PDevi — Thu, 03 Oct 2019 19:53:24 GMT

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.

Re: Drop observations having null values

novinosrin — Thu, 03 Oct 2019 19:57:36 GMT

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 ' ';

Re: Drop observations having null values

PeterClemmensen — Thu, 03 Oct 2019 19:59:39 GMT

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;

Re: Drop observations having null values

hashman — Fri, 04 Oct 2019 02:25:44 GMT

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

Re: Drop observations having null values

PDevi — Sat, 05 Oct 2019 02:09:12 GMT

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

Re: Drop observations having null values

PDevi — Sat, 05 Oct 2019 02:10:22 GMT

Thanks @PeterClemmensen. Appreciate your help.