topic Re: Some simple data management questions (missing values, subsetting) in SAS Procedures

Some simple data management questions (missing values, subsetting)

trekvana — Tue, 14 Sep 2010 17:43:59 GMT

Hello all-

Firstly, suppose I have a data set that has many variables with missing values and i want to only keep the observations with no missing values. Is there a way I can tell SAS to check all the variables in my data set as opposed to one at a time as in:

data weight1;
set weight;
where (var1 ^=. AND var2^=. AND var3^=. etc);
run;

Secondly, how can I add a new row of data to an existing dataset. For example, if I have a dataset and I want to add a new observation var1=x, var2=y, var3=z, etc., how can I do this?

Thanks
Geo

Re: Some simple data management questions (missing values, subsetting)

Paige — Tue, 14 Sep 2010 18:09:10 GMT

You can use the SUM function to add all the numeric variables in a row; if the result is missing, then all variables in the row are missing.

data a;
set sashelp.class;
rowsum=sum(of _numeric_);
run;

You can add a new row with the OUTPUT statement in a SAS data step

data a;
set sashelp.class end=eof;
output;
if eof then do;
age=14;
height=56;
weight=99;
output;
end;
run;

Re: Some simple data management questions (missing values, subsetting)

trekvana — Tue, 14 Sep 2010 18:21:02 GMT

Paige-

What if some of my variables are categorical and are missing?

Re: Some simple data management questions (missing values, subsetting)

Paige — Tue, 14 Sep 2010 18:46:32 GMT

I don't really have a simple example in that case. Probably you need to use ARRAYs.

But I have a question for you. Why make an effort to delete rows with all missing values? Leaving them in the data set doesn't hurt anything, does it?

Re: Some simple data management questions (missing values, subsetting)

art297 — Tue, 14 Sep 2010 19:49:10 GMT

Please still answer Paige's question but, if you need to identify records that have missing values AND you are using SAS 9.2, take a look at the cmiss function.

Art

Re: Some simple data management questions (missing values, subsetting)

sbb — Tue, 14 Sep 2010 20:31:09 GMT

One example with using CMISS and NMISS is demonstrated below, along with the special variable-type array references (all variables of a type).

9 data _null_;
10 retain a1 . a2 0 b1 'x' b2 'y';
11 if cmiss(of _character_) then put 'char-yep';
12 if nmiss(of _numeric_) then put 'num-yep';
13 run;

num-yep
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

Scott Barry
SBBWorks, Inc.

Re: Some simple data management questions (missing values, subsetting)

art297 — Tue, 14 Sep 2010 20:41:16 GMT

Scott,

I don't have 9.2, thus can't test it, but I was under the impression that CMISS handled BOTH numeric and character. Thus, I would think that one could use:
CMISS (of _all_)

Art

Re: Some simple data management questions (missing values, subsetting)

sbb — Tue, 14 Sep 2010 20:55:27 GMT

Thanks for the tip, Art - I tested it with SAS 9.2 and you are correct.

Scott Barry
SBBWorks, Inc.

Re: Some simple data management questions (missing values, subsetting)

trekvana — Tue, 14 Sep 2010 22:03:00 GMT

Paige-

I am working with the proc mixed statement doing a longitudinal analysis and I have missing data for some of my variables which are both continuous and categorical.

I know proc mixed automatically removes the missing observations but I wanted to have a separate data set which contained all the non-missing values for procs that dont play nice with missing values (such as proc iml)

Art and Scott-
Thanks for the help. Here what I did and it worked out nicely:

data weight1;
set weight1;
if cmiss(of _all_) then miss='Y';
else miss='N';
run;

data nomiss;
set weight1;
where miss='N';
drop miss;
run;

Re: Some simple data management questions (missing values, subsetting)

Paige — Wed, 15 Sep 2010 12:54:06 GMT

which can be shortened to

data nomiss;
set weight1;
if cmiss(of _all_) then delete;
run;

Re: Some simple data management questions (missing values, subsetting)

chang_y_chung_hotmail_com — Wed, 15 Sep 2010 14:29:23 GMT

which, in turn, can be shortened to: 🙂
[pre]
data nomiss;
set weight1;
if ^cmiss(of _all_);
run;
[/pre]