topic Re: Sum function and ignoring characters in SAS Programming

Sum function and ignoring characters

dustychair — Wed, 20 Jul 2022 20:47:32 GMT

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

Re: Sum function and ignoring characters

Kurt_Bremser — Wed, 20 Jul 2022 20:57:45 GMT

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.

Re: Sum function and ignoring characters

s_lassen — Thu, 21 Jul 2022 09:08:31 GMT

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;

Re: Sum function and ignoring characters

Tom — Thu, 21 Jul 2022 15:11:42 GMT

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;