topic Replace missing values with zero for a data contains a lots of variables in SAS Programming

Replace missing values with zero for a data contains a lots of variables

France — Thu, 06 Sep 2018 14:12:12 GMT

I would like to replace the missing value with zero for a data contains 132 variables by following codes,

DATA Step5.ar_Number_of_cit_end ;
   SET Step4.Number_of_cit_end ;
   ARRAY application(132) application20001 - application201512 ;
   DO i= 1 TO 132;
   IF application(i) eq . THEN application(i) = 0 ;
   END;
   DROP i;
RUN;

but the result shows that,

14   DATA Step5.ar_Number_of_cit_end ;
15      SET Step4.Number_of_cit_end ;
16      ARRAY application(132) application2001 - application201512 ;
ERROR: Too many variables defined for the dimension(s) specified for the array application.
17      DO i= 1 TO 132;
18      IF application(i) eq . THEN application(i) = 0 ;
19      END;
20      DROP i;
21   RUN;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      18:7
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set STEP5.AR_NUMBER_OF_CIT_END may be incomplete.  When this step was stopped there
         were 0 observations and 133 variables.
NOTE: DATA statement used (Total process time):
      real time           5.90 seconds
      cpu time            0.09 seconds

I read the other subject that has a similar question and shows the following codes, but I really don't know how to put my variable into this codes,

data "database name";  
    set "database name";
    array nm(*) _numeric_ ;
    do _n_ = 1 to dim(nm);
    nm(_n_) = coalesce(nm(_n_),0);
    END;
    DROP i;
RUN;

Could you please give me some suggestions?

thanks in advance.

Re: Replace missing values with zero for a data contains a lots of variables

RW9 — Thu, 06 Sep 2018 14:20:15 GMT

If they are the only variables of a numeric type in your dataset, then you can use _numeric_:

data want;
  set have;
  array n{*} _numeric_;  /* gets all numeric variables into array */
  do i=1 to dim(n);         /* Do for all elements */
    if n{i}=. then n{i}=0;
  end;
run;

If its not then you need to specify them:

data want;
  set have;
  array n{*} application20001-application201501;
  do i=1 to dim(n);         /* Do for all elements */
    if n{i}=. then n{i}=0;
  end;
run;

However two things. First, if this is just for a report you can use:

options missing=0;

This will set an option which will display missing as zero - note affects whole session!

Secondly and more importantly, it is not a good data modelling technique to use long (transposed) datasets. Put you data into a column, and have an actual date column alongside it:

DT VAL

200001 xy

200101 xy

...

This is far simpler model to work with, and makes working with the date information far easier. At report time you can simply tranpose the data to get transposed data, best of all worlds.

Re: Replace missing values with zero for a data contains a lots of variables

Tom — Thu, 06 Sep 2018 14:23:31 GMT

The list of variables does not match the dimension you specified. You told SAS to make an array with 132 variables in it and then gave it a range of names that has over 180 thousand names in it. (201,512 - 20,001 + 1)

You don't need to tell SAS what dimension to use. It can count the names for you. But you do need to use the real list of names.

Perhaps you can use a positional list? Use double hyphen.

ARRAY application  application20001 -- application201512 ;

Perhaps you can use a prefix to find the list of names? Use a trailing colon.

ARRAY application  application20: ;

Also your variable types need to match. Period for missing and 0 are valid for numeric variables. If the variables are character instead then use ' ' and '0' instead. But if you are lucky the warning about converting character to number was just caused by the earlier error with defining the array,