topic Re: Counting missing values in SAS Programming

Counting missing values

dmarques1998 — Mon, 07 Jun 2021 10:58:48 GMT

Hi,

I have the following data:

data example;
  input id var1 var2 var3 var4 var5 var6 var7 var8 var9 var10 var11 var12;
cards;
A	1 2 3 . . . . . 1 1 1 3
B	3 3 2 1 3 2 1 . . . . .
C	. . . . 1 2 3 1 2 3 2 .
D	. . . . 3 1 1 . 3 . 1 .
E	. . . . 1 3 1 3 1 3 . .
F	3 2 1 . . . . . 1 1 1 3

;

And I want to count the number of missing values, however I want to start counting only when we start having values. For example, I want the following output:

ID Number of missing values:

A 5

B 5

C 1

D 3

E 2

F 5

Thanks

Re: Counting missing values

Kurt_Bremser — Mon, 07 Jun 2021 11:40:21 GMT

data want;
set  example;
array vars{*} var1-var12;
nomiss = 0;
number_miss = 0;
do _n_ = 1 to dim(vars);
  if vars{_n_} then nomiss = 1;
  if nomiss and vars{_n_} = . then number_miss + 1;
end;
drop nomiss;
run;

Untested, posted from my tablet.

Edit: fixed a typo (thrn -> then)

Re: Counting missing values

dmarques1998 — Mon, 07 Jun 2021 11:31:35 GMT

From that way, it is counting all the missing values!

Re: Counting missing values

PaigeMiller — Mon, 07 Jun 2021 11:38:20 GMT

@dmarques1998, did you even try the code from @Kurt_Bremser? It gives the exact answer you want (after correcting a typographical error).

Re: Counting missing values

dmarques1998 — Mon, 07 Jun 2021 11:47:28 GMT

yes, and adapting to my code, give me the exact number of missing values, and not the number of missing values that I want

Re: Counting missing values

PaigeMiller — Mon, 07 Jun 2021 11:50:43 GMT

Okay, you said:

For example, I want the following output:

ID Number of missing values:

A 5

B 5

C 1

D 3

E 2

F 5

and those are the numbers I see when I run the code.

If you're not getting the above answers, then show us the exact code you are using.

Re: Counting missing values

dmarques1998 — Mon, 07 Jun 2021 11:59:54 GMT

this is the Note/warning that appear in the log :

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
30:6
NOTE: Invalid numeric data, 'XXXX' , at line 30 column 6.
NOTE: Invalid numeric data, 'XXXXX' , at line 30 column 6.
WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed.

Re: Counting missing values

PaigeMiller — Mon, 07 Jun 2021 12:02:26 GMT

Please show us the ENTIRE log, with nothing chopped out. Please click on the </> icon and paste your log as text into the window that appears.

Re: Counting missing values

Ksharp — Mon, 07 Jun 2021 12:05:53 GMT

data example;
infile cards expandtabs truncover;
  input id $ var1 var2 var3 var4 var5 var6 var7 var8 var9 var10 var11 var12;
cards;
A	1 2 3 . . . . . 1 1 1 3
B	3 3 2 1 3 2 1 . . . . .
C	. . . . 1 2 3 1 2 3 2 .
D	. . . . 3 1 1 . 3 . 1 .
E	. . . . 1 3 1 3 1 3 . .
F	3 2 1 . . . . . 1 1 1 3
; 
options missing='.';
data want;
 set example;
 want=lengthn(scan(cats(of var:),-1,,'d'));
run;

Re: Counting missing values

dmarques1998 — Mon, 07 Jun 2021 12:17:52 GMT

Re: Counting missing values

Kurt_Bremser — Mon, 07 Jun 2021 12:16:34 GMT

Did it EVER occur to you that the type of variables might be important??

I can only develop code for data that I see; if you want to play practical jokes, I'm outta here.

Re: Counting missing values

PaigeMiller — Mon, 07 Jun 2021 12:18:14 GMT

Thanks @dmarques1998 . Obviously, you are not working with the data that you showed in your original post. We would need to see (a portion of) your actual data, provided as you have done previously as SAS data step code.

Re: Counting missing values

FreelanceReinh — Mon, 07 Jun 2021 12:33:40 GMT

Hi @dmarques1998,

So, you're working with character variables. An alternative to using a DO loop (which is a perfectly valid approach) is to use SAS functions.

Example:

data have;
input (id var1-var12)($);
cards;
A	1 2 3 . . . . . 1 1 1 3
B	3 3 2 1 3 2 1 . . . . .
C	. . . . 1 2 3 1 2 3 2 .
D	. . . . 3 1 1 . 3 . 1 .
E	. . . . 1 3 1 3 1 3 . .
F	3 2 1 . . . . . 1 1 1 3
G	. . . . . . . . . . . .
H	1 1 1 1 1 1 1 1 1 1 1 1
;

data want;
set have;
array _v[*] var1-char-var12; /* name range list, assuming that real variable names are not var1 etc. */
if cmiss(of _v[*])=dim(_v) then n_miss=.; /* or whatever you want n_miss to be in this case */
else n_miss=cmiss(of _v[*])-whichc(coalescec(of _v[*]), of _v[*])+1;
run;