Sum function and ignoring characters

[dustychair]

Hi all,

I am trying to sum some variables which have scores 0-1-2 and characters 'Z' or 'M'. I want to sum numbers and ignore those character responses. Any ideas how i can do that?

Thanks

[Kurt_Bremser]

Convert the variables to numeric; use the special missing values .M and .Z for the characters.

The SUM function will then work correctly, as will procedures like MEANS.

[s_lassen]

You can also create in informat and use that in SQL:

data have;
input ID score $;
cards;
1 1
1 3
1 M
2 Z
2 2
2 2
2 3
;run;

proc format;
  invalue score
  '1'=1
  '2'=2
  '3'=3
  'M'=.M
  'Z'=.Z
  other=_ERROR_
  ;
run;

proc sql;
  create table want as
  select id,sum(input(score,score.)) as score
  from have
  group by id;
quit;

Of course, you could also use the informat when reading the data in the first place, you can then use PROC SUMMARY etc. on the score variable, which is now numeric: 

data have;
  input ID score score.;
cards;
1 1
1 3
1 M
2 Z
2 2
2 2
2 3
;run;

[Tom · Accepted solution]

Make a NUMERIC variable.  Now you can do any arithmetic you want with the variable.  

There is no need to define a special INFORMAT. Use the MISSING statement to tell SAS which single letter text strings to interpret as representing the corresponding special missing numeric values.  

missing M Z ;
data have;
  input var ;
cards;
1
0
2
M
Z
;
proc print;
run;
proc means n min max mean;
run;