topic Re: Missing value in a SAS dataset in SAS Enterprise Guide

Missing value in a SAS dataset

alepage — Fri, 13 Jul 2018 14:05:31 GMT

Hello,

I have found a lot of examples about missing values, either numerical or string but I did not find exactly what I am looking for.

I looking for something sample.

My dataset could contains a lot of observations and many variables of both kinds numerical and string.

So, I want to set a flag to one is there is no missing value (numerical or character) in the dataset and the flag to zero if there is some missing value without regards of the kind of values.

Does someone could have a nice idea?

I am using SAS EG 7.11

Regards,

Re: Missing value in a SAS dataset

PaigeMiller — Fri, 13 Jul 2018 14:25:55 GMT

So, I want to set a flag to one is there is no missing value (numerical or character) in the dataset

Please clarify:

You want one flag for the entire data set?

Or do you want a flag for each observation?

Or do you want a flag for each variable?

Re: Missing value in a SAS dataset

SuryaKiran — Fri, 13 Jul 2018 15:07:54 GMT

You can use arrays to find the missing Character and Numeric values and then assign the flag.

data test;
infile datalines dlm=',' dsd missover;
input char1 $ char2 $ num1 num2;
datalines;
,char,1,
char,char,.,2
char,char,1,2
;
run;

data want (drop=i j);
set test;
array str(*) _character_;
array nbr(*) _numeric_;
do i=1 to dim(str);
if missing(str(i)) then flag=1;
end;
do j=1 to dim(nbr);
if missing(nbr(j)) then flag=1;
end;
if missing(flag) then flag=0;
run;

Re: Missing value in a SAS dataset

MelodieRush — Fri, 13 Jul 2018 15:21:56 GMT

You can use functions available in SAS to help you with this as well, depending on what you are trying to accomplish.

missing_flag = missing(VAR1); /* works for both numeric and character varaibles) */

num_miss=nmiss(NUMVAR1, NUMVAR2, NUMVAR3);

char_miss=cmiss(CHARVAR1, CHARVAR2, CARVAR3);

Re: Missing value in a SAS dataset

alepage — Fri, 13 Jul 2018 15:45:47 GMT

Hello,

I already know those functions. But how can we use those functions when we have around 100 variables.

I don't want to enter the name of each variable in the code...

Re: Missing value in a SAS dataset

alepage — Fri, 13 Jul 2018 15:47:51 GMT

Hello,

I will say both.

First I am interested to check if there is a missing value in the dataset. Then if so, which variable and observation?

Thanks in advance for your help.

Re: Missing value in a SAS dataset

FreelanceReinh — Fri, 13 Jul 2018 16:25:03 GMT

If you want to check all variables in the dataset, it's fairly simple:

data want;
retain nm 0;
set sashelp.heart;
nm=cmiss(of _all_);
if nm;
run;

This selects all observations with at least one missing value for further inspection and counts the missing values per observation in variable NM.

Re: Missing value in a SAS dataset

Tom — Fri, 13 Jul 2018 16:29:48 GMT

@FreelanceReinh wrote:

If you want to check all variables in the dataset, it's fairly simple:
data want;
retain nm 0;
set sashelp.heart;
nm=cmiss(of _all_);
if nm;
run;
This selects all observations with at least one missing value for further inspection and counts the missing values per observation in variable NM.

Note that the new variable NM is included in the _ALL_ variable list. This program handles that by using the RETAIN statement with an initial value to insure that NM is never missing. You could also just subtract one from the result of the CMISS() function to account for the missing value of NM.

data want;
  set sashelp.heart;
  nm=cmiss(of _all_) - 1;
run;

Re: Missing value in a SAS dataset

MelodieRush — Fri, 13 Jul 2018 18:08:37 GMT

You can certainly use arrays as suggested in other solutions.

array nums(*) _numeric_;

array char(*) $ _character_:

And depending on how your dataset variables are named and arranged you can use short cuts like are described in this http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000695105.htm

VAR1-VARn or if all your numeric or character are ordered together you can use the numvarfirst-numvarlast, etc...

so you could use nummissing = nmiss(VAR1-VAR100) instead of typing all the variable names.

Re: Missing value in a SAS dataset

alepage — Tue, 17 Jul 2018 19:38:06 GMT

Hello,

If I am interested to set a flag to one if there is at lease one missing value (num or string) in the dataset is there a better way to do that compare to my code below.

Regards,

Re: Missing value in a SAS dataset

FreelanceReinh — Tue, 17 Jul 2018 22:31:52 GMT

Hello @alepage,

If I may put in my two cents:

I would include the %GLOBAL and %LET statements in the macro, so that they are not forgotten.
The data step will be very inefficient if the input dataset is huge and has a missing value in one of its first few observations. So, you'd better stop checking for missing values as soon as one is found. This can be implemented in various ways. For example:
```
data _null_;
set &Path..&Name.;
if cmiss(of _all_) then do;
  call symputx('flag',1);
  stop;
end;
run;
```
As you see, variable NM can be omitted if macro variable FLAG is the only result you need.