Desktop productivity for business analysts and programmers

Missing value in a SAS dataset

Accepted Solution Solved
Reply
Regular Contributor
Posts: 166
Accepted Solution

Missing value in a SAS dataset

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,

 


Accepted Solutions
Solution
‎07-13-2018 02:30 PM
SAS Employee
Posts: 116

Re: Missing value in a SAS dataset

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.

View solution in original post


All Replies
Respected Advisor
Posts: 3,249

Re: Missing value in a SAS dataset

[ Edited ]

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?

 

 

--
Paige Miller
Regular Contributor
Posts: 166

Re: Missing value in a SAS dataset

Posted in reply to PaigeMiller

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.

 

 

Trusted Advisor
Posts: 1,317

Re: Missing value in a SAS dataset

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.

Super User
Super User
Posts: 8,261

Re: Missing value in a SAS dataset

Posted in reply to FreelanceReinhard

@FreelanceReinhard 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;
Regular Contributor
Posts: 166

Re: Missing value in a SAS dataset

Posted in reply to PaigeMiller

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,

 

 

 

 

 

Trusted Advisor
Posts: 1,317

Re: Missing value in a SAS dataset

[ Edited ]

Hello @alepage,

 

If I may put in my two cents:

  1. I would include the %GLOBAL and %LET statements in the macro, so that they are not forgotten.
  2. 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.
PROC Star
Posts: 617

Re: Missing value in a SAS dataset

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;
Thanks,
Suryakiran
SAS Employee
Posts: 116

Re: Missing value in a SAS dataset

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);

 

 

Regular Contributor
Posts: 166

Re: Missing value in a SAS dataset

Posted in reply to MelodieRush

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...

Solution
‎07-13-2018 02:30 PM
SAS Employee
Posts: 116

Re: Missing value in a SAS dataset

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 323 views
  • 7 likes
  • 6 in conversation