topic Re: How to use array to replace missing value in multiple variables to "NA" in SAS Programming

How to use array to replace missing value in multiple variables to "NA"

stataq — Tue, 24 Sep 2024 00:15:36 GMT

Hello, I would like to covert a set of numerical variables to character and replace . to "NA". I tried to use array for this task. However my missing is still show up as . in stead of "NA". What did I do wrong?

Please see the sample data and my codes below:

data df1;
	a = .; b=1; c=1; output;
	a = 1; b=.; c=1; output;
	a = 1; b=1; c=.; output;
run;

data want ;
set df1;
  array _invars{3} a b c;
    array _invals{3} _inval1 _inval2 _inval3;
  do i=1 to 3;
		 if  _invars{i} gt .z   then  _invals{i}=put( _invars{i},5.2)	;
	  else 	  _invals{i}= "NA"  ;
	  end;		
  run ;

Re: How to use array to replace missing value in multiple variables to "NA"

A_Kh — Tue, 24 Sep 2024 00:46:03 GMT

You probably get notes liek this : "NOTE: Invalid numeric data, 'NA' , at line 49 column 27".
You just missed to declare the datatype of the new variables. You need a character variables, so $x needs to be declared in front of the new variables created by array.
See the below:

data want ;
set df1;
  array _invars{3} a b c;
    array _invals{3} $8 _inval1 _inval2 _inval3;
  do i=1 to 3;
		 if  _invars{i} gt .z   then  _invals{i}=put( _invars{i},5.2)	;
	  else 	  _invals{i}= "NA"  ;
	  end;		
  run ;

Re: How to use array to replace missing value in multiple variables to "NA"

stataq — Tue, 24 Sep 2024 01:01:09 GMT

@A_Kh Thanks for quick reply. One side question, is there a quicker method to name the new variables? In my codes, I listed

_inval1 _inval2 _inval3

Is it a way to name a set of variables with prefix _inval given we know the number of new variables=3?

Re: How to use array to replace missing value in multiple variables to "NA"

SASKiwi — Tue, 24 Sep 2024 01:10:10 GMT

You can display NA in numeric variables with missing values without converting them to character by using a custom format:

proc format;
  value myMissing
  low-high = [best12.]
  other = 'NA'
  ;
run;

data MyData;
  set sashelp.class;
  if name in ('John','Mary') then age = .;
  if Name in ('Alice','Philip') then weight = .;
  format age weight MyMissing.;
run;

proc print data = MyData;
run;

Re: How to use array to replace missing value in multiple variables to "NA"

A_Kh — Tue, 24 Sep 2024 01:16:48 GMT

I usually list variables like this:

array _invals{3} $8 _inval1 - _inval3;

or just omit new variable names when the range is specified with array name, like "_invals [3]" in this case. SAS takes new variable names from the declared array name (_invals) and creates the number of new variables equal to a specified range (3) and appends sequential numbers (eg. 1 to n.) to that.
see this :

data want ;
set df1;
  array _invars{3} a b c;
  array _invals{3} $8;
  do i=1 to 3;
		 if  _invars{i} gt .z   then  _invals{i}=put( _invars{i},5.2)	;
	  else 	  _invals{i}= "NA"  ;
	  end;		
  run ;

Re: How to use array to replace missing value in multiple variables to "NA"

Patrick — Tue, 24 Sep 2024 02:42:57 GMT

You could use a format for recoding and if only required for display (print) purposes then even apply it directly to the numerical variables.

data df1;
  a = .; b=1; c=1; output;
  a = 1; b=.z; c=1; output;
  a = 1; b=1; c=.; output;
run;

proc format;
  value num2char
    low-high   =[f16.]
    other ='NA'
    ;
run;

/* if only for display purposes */
proc print data=df1;
  format a b c num2char.;
run;

/* if storing recoded value in character vars */
data want;
  set df1;
  array _invars{3} a b c;
  array _inval_{3} $16;

  do i=1 to dim(_invars);
    _inval_[i]=put(_invars[i],num2char.);
  end;
run;

proc print data=want;
run;