topic Re: Delete variables only if all are missing in SAS Programming

Delete variables only if all are missing

Babloo — Tue, 18 Sep 2018 13:03:34 GMT

I want to delete the variables only if all of them (INBNPR,INBINK,INBFRK) are missing using where clause. In this case I want the same output as I got in the previous dataset qisfr15 but I end up with 0 observations. Could you please guide me?

data qisfr15;
INBNPR=0;
INBINK=2;
INBFRK=.;
run;

proc sql;
create table test as select * from qisfr15
where INBNPR ne . and INBINK ne . and INBFRK ne . ;
quit;

Log:

27         data qisfr15;
28         INBNPR=0;
29         INBINK=2;
30         INBFRK=.;
31         run;

NOTE: The data set WORK.QISFR15 has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

32         
33         proc sql;
34         create table test as select * from qisfr15
2                                                          The SAS System                          11:55 Tuesday, September 18, 2018

35         where INBNPR ne . and INBINK ne . and INBFRK ne . ;
NOTE: Table WORK.TEST created, with 0 rows and 3 columns.

36         quit;

Re: Delete variables only if all are missing

Tom — Tue, 18 Sep 2018 13:25:06 GMT

Your query is keep the observations where it is true that ALL three variables are NOT missing, so if any of the three IS missing then exclude that observation.

You want to keep the observations where it is NOT true that ALL three variables are missing.

where not (INBNPR=. and INBINK=. and INBFRK=.)

Our you can use De Morgan's law and convert to an expression using OR instead. So look for observations where at least one of them is not missing.

where INBNPR ne . or INBINK ne . or INBFRK ne .

Re: Delete variables only if all are missing

Jagadishkatam — Tue, 18 Sep 2018 13:45:07 GMT

proc sql;
create table test as select * from qisfr15
where nmiss(INBNPR,INBINK,INBFRK)<3;
quit;

Re: Delete variables only if all are missing

PaigeMiller — Tue, 18 Sep 2018 13:51:54 GMT

Although you have asked to delete VARIABLES, your code and the code of the other people are deleting OBSERVATIONS.

Do you want to delete VARIABLES? Or do you want to delete OBSERVATIONS?

Re: Delete variables only if all are missing

Reeza — Tue, 18 Sep 2018 15:09:43 GMT

If you want to check if all values of a variable are missing PROC FREQ with the NLEVELS data set is the best method. You cannot drop variables within a WHERE statement, you can only drop observations.

Re: Delete variables only if all are missing

Babloo — Tue, 18 Sep 2018 17:15:31 GMT

I want to delete the observations if all the variables mentioned in the
initial post are missing.

Re: Delete variables only if all are missing

Reeza — Tue, 18 Sep 2018 17:20:39 GMT

Then your code is correct.

Re: Delete variables only if all are missing

ChrisNZ — Tue, 18 Sep 2018 22:33:12 GMT

In that case, @Jagadishkatam's answer is the most elegant in my opinion.

Also please change the post title to reflect your actual question (delete observations).