I have to delete all observations which has missing values in all variables; irrespective of the datatype. This should be accomplished in a data step in a single statement, without using macro.
I tried with _all_. But, i guess The SAS System never allows this to be assigned to a variable. I also tried with cmiss & nmiss, but these 2 functions are specific to datatype. I tried with sum function which will throw a "invalid numeric data" note when character variable is encountered.
Yes, you can declare an ARRAY using the _ALL_ operand for the variable name and then test using the MISSING function in a DO / END loop. If all variables have a missing condition, then DELETE the observation.
no array required.
Rather than re-write all your table, you can update-in-place with the modify statement and remove rows where the count of non-missing numerics is zero and the concatenation of all character columns is blank with:
data your.data ;
modify your.data ;
if n( of _numeric_ ) = 0 and cats( of _character_ ) = ' ' then remove ;
Does this do what is needed?
The data step creating a new table with the missing obs deleted is very similar
(a new dataset name in the data statement, replace "modify" with "set", and "remove" with "delete")
that systax error occurs when there are no character variables, and you indicated that _all_ involved more than one data type implying that there are character variables in that test case.
The solution only requires a small extension to that use of cats()
and cats( of _character_, ' ' )
Perhaps there needs to be a similar alteration to the use of the N() function
n( of _numeric_, . )
Yes CAT does accept numerics but the technique won't work if the numerics have special missing values. Better to stick with Peter's suggestion.
OBS 4 below has all missing variable as does OBS 3.
missing a b;
input a$ b c$ d e f;
*if compress(cats(of _all_), ". ") eq ' ' then flag=1;
if n(of _numeric_,.) eq 0 and cats(of _character_,' ') eq ' ' then flag=1;
a b c 1 2 3
2 a c 1 2 3
. . . . . .
. a . a b b