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.
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;
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 ' ';
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.