How to remove empty cases in a dataset in SAS

Question

I want to remove ALL pure observations from the dataset. I only know how to get rid of spaces from one variable:

data a; set data(where=(var1 ne .)) ; run; 

Here I am setting up a new dataset without spaces from var1. But how to do this when I want to get rid of ALL the gaps in the entire data set?

Thanks in advance for your answers.

Answer 1

If you are trying to get rid of lines where ALL variables are missing, this is pretty simple:

 /* Create an example with some or all columns missing */ data have; set sashelp.class; if _N_ in (2,5,8,13) then do; call missing(of _numeric_); end; if _N_ in (5,6,8,12) then do; call missing(of _character_); end; run; /* This is the answer */ data want; set have; if compress(cats(of _all_),'.')=' ' then delete; run; 

Instead of compressing, you can also use OPTIONS MISSING=' '; in advance.

If you want to delete ALL rows with ANY missing value, you can use the NMISS / CMISS functions.

 data want; set have; if nmiss(of _numeric_) > 0 then delete; run; 

or

 data want; set have; if nmiss(of _numeric_) + cmiss(of _character_) > 0 then delete; run; 

for all char + numeric variables.

Answer 2

You can do something like this:

 data myData; set myData; array a(*) _numeric_; do i=1 to dim(a); if a(i) = . then delete; end; drop i; 

This will scan all numerical variables and delete the observation, where it will find the missing value

Answer 3

Here you go. This will work regardless of the variable being a character or number.

 data withBlanks; input a$ xyz; datalines; a 1 2 3 b 1 . 3 c . . 3 . . . d . 2 3 e 1 . 3 f 1 2 3 ; run; %macro removeRowsWithMissingVals(inDsn, outDsn, Exclusion); /*Inputs: inDsn: Input dataset with some or all columns missing for some or all rows outDsn: Output dataset with some or all columns NOT missing for some or all rows Exclusion: Should be one of {AND, OR}. AND will only exclude rows if any columns have missing values, OR will exclude only rows where all columns have missing values */ /*get a list of variables in the input dataset along with their types (ie, whether they are numericor character type)*/ PROC CONTENTS DATA = &inDsn OUT = CONTENTS(keep = name type varnum); RUN; /*put each variable with its own comparison string in a seperate macro variable*/ data _null_; set CONTENTS nobs = num_of_vars end = lastObs; /*use NE. for numeric cols (type=1) and NE '' for char types*/ if type = 1 then call symputx(compress("var"!!varnum), compbl(name!!" NE . ")); else call symputx(compress("var"!!varnum), compbl(name!!" NE '' ")); /*make a note of no. of variables to check in the dataset*/ if lastObs then call symputx("no_of_obs", _n_); run; DATA &outDsn; set &inDsn; where %do i =1 %to &no_of_obs.; &&var&i. %if &i < &no_of_obs. %then &Exclusion; %end; ; run; %mend removeRowsWithMissingVals; %removeRowsWithMissingVals(withBlanks, withOutBlanksAND, AND); %removeRowsWithMissingVals(withBlanks, withOutBlanksOR, OR); 

Outout ofOutBlanksAND:

 axyz a 1 2 3 f 1 2 3 

Exit with OutBlanksOR:

 axyz a 1 2 3 b 1 . 3 c . . 3 e 1 . 3 f 1 2 3