topic Re: Indicate if row had missing values in selection of columns in SAS Programming

Indicate if row had missing values in selection of columns

cmues — Wed, 01 Dec 2021 14:59:08 GMT

Hi everyone,

I have a dataset that roughly looks like the one below.

data have;
 infile cards delimiter='|';
input X1 $ V1 $ V2 $ V3 $;
cards;
	|a1|	|a1	|
	||c2,a1|	c3,c2	|
	||b2|		|
te	||	|	|
;
run;

Now I want to create a new column miss that indicates whether or not a row had any observation in V1, V2, or V3.

Row 1, 2 and 3 all have values in either V1, V2, V3 or some combination, so miss would here be 1.

Row 4 has no values in V1-V3, so miss would be 0.

Important is that the solution is dynamic, since the dataset grows on a frequent basis.

I think an additional issue for my data is that i'm not sure these empty spaces are actually flagged as missing? How would I go about checking that? i've tried some of the missing functions in SAS, but i find them so unintuitive that I might as well just be doing it wrong and getting confusing results.

Thanks a bunch.

Re: Indicate if row had missing values in selection of columns

PaigeMiller — Wed, 01 Dec 2021 15:02:59 GMT

Use the CMISS function to count the number of missings.

Re: Indicate if row had missing values in selection of columns

mkeintz — Wed, 01 Dec 2021 15:11:26 GMT

The statement

  miss=(cats(of v1-v3)^=' ');

assigns a 1 to MISS if the expression cats(of v1-v3)^=' ' is true, or a 0 otherwise. The CATS function concatenates the characters of V1, V2, and V3 (while stripping all leading or trailing blanks from the components). If all of them are missing (i.e. blank) then the CATS result is blank, making the expression true. So

data have;
 infile cards delimiter='|';
input X1 $ V1 $ V2 $ V3 $;
cards;
	|a1|	|a1	|
	||c2,a1|	c3,c2	|
	||b2|		|
te	||	|	|
;
run;
data want;
  set have;
  miss=cats(of v1-v3)^=' ';
run;

will do what you want.

Re: Indicate if row had missing values in selection of columns

cmues — Wed, 01 Dec 2021 15:11:53 GMT

Sorry, I really do not understand at all how to apply these functions to create a column or even a summary. Nor do I feel the documentation is very clear on it. Is it part of a DATA or PROC?

Re: Indicate if row had missing values in selection of columns

PaigeMiller — Wed, 01 Dec 2021 15:18:15 GMT

@cmues wrote:

Sorry, I really do not understand at all how to apply these functions to create a column or even a summary. Nor do I feel the documentation is very clear on it. Is it part of a DATA or PROC?

In a data step

count=cmiss(of var1-var3);