Summing characters in SAS

Question

I have a (pretty large) dataset in SAS where one of the columns contains data that looks like this -

Column Name
8    
4    
13    
NA    
NA    
3    
5

etc..

Because of the NA's in the columns (and there are quite a few of them), SAS recognises the entire column as containing character variables. However, I want to perform some mathematical operations on the numbers within this column e.g. SUM, but because SAS can't perform the SUM function on character variables, this is proving to be quite difficult. Is there a way to make SAS think of this column as being numeric?

Thanks for your help!

Answer 1

You mention SUM so there are two possibilities, SQL SUM function over an aggregate group, or the SUM statistic in a SAS Procedure, such as Proc MEANS, SUMMARY, UNIVARIATE, REPORT, TABULATE, etc.

In SQL a SUM of a computed value (the conversion from character representation of a number to a numeric value) can be performed directly. Suppose the column in question is named amount

data have;
length amount $2;
input amount @@; datalines;
8 4 13 NA NA 3 5
;

proc sql;
  create table want as
  select 
    SUM(
      input(amount,?best12.)  /* computed value is conversion through INPUT() */
    ) as amount_total
    %* The ? before the informat (best12.) suppresses log messages such as
    %* NOTE: Invalid string and 
    %* NOTE: Invalid argument;
  from have;

For the case of other procedures, they will require a data source that delivers the column converted to a numeric or new numeric variable based on the original character variable. There are two ways to provide that data source:

• As a VIEW
• As a DATA set

* view;
data have_view / view=have_view;
  set have;
  amount_num = input(amount,?best12.);
run;
proc means noprint data=have_view;
  var amount_num;
  output out=want_2 sum=amount_total;
run;

* or data;
data have_num;
  set have;
  amount_num = input(amount,?best12.);
run;
proc means noprint data=have_num;
  var amount_num;
  output out=want_2 sum=amount_total;
run;

See SAS Proc Import CSV and missing data for a macro that converts a character variable in place, and does not create new variable names. With such a macro the non-numeric original values (such as NA, ??) are 'lost' because they become missing values (.)

Answer 2

If you only have one text value to deal with, you can use TRANWRD to replace it with either '0' or '.' - depending how you want to handle your NA null values.

OR... If you want to make sure you remove all text characters from the column values, you can use the COMPRESS function with the 'KD' modifier to 'Keep Digits' only. (A full list of compress modifiers can be found here. They're super useful when cleaning up text).

Then you can 'convert' the new column to numeric by applying a *1 multiplier to it, or use an INPUT statement (proc sql won't like the *1 multipier work around).

data your_dataset;
    input text_column $ @@;
    datalines;
8    4    13    NA    NA    3    5
;
run;

/* update dataset to have numeric version of column... */
data updated_dataset;
    set your_dataset;
    num_column1 = tranwrd(upcase(text_column), 'NA', '0') * 1;
    num_column2 = compress(text_column, ,'kd') * 1;
run;

/* ..or leave your dataset unchanged and just show sums using proc sql */
proc sql;
    create table show_sums as
    select   sum(input(tranwrd(upcase(text_column), 'NA', '0'), 8.)) as sum1
            ,sum(input(compress(text_column, ,'kd'), 8.)) as sum2
    from your_dataset
    ;
quit;