Find and replace for all numeric variables

Reply
Contributor
Posts: 29

Find and replace for all numeric variables

I have a table which contains a large amount of numeric variables.

I need to check each variable if it contains a certant value, and if it does, replace it with something else.

What's the easiest way todo this (given the amount of variables i need to go through)?

Best

Kasper

Super Contributor
Posts: 644

Re: Find and replace for all numeric variables

If you table is not too large and you have a recent version of MS Excel, and

if the numbers you want to replace are not (and cannot be) substrings of other variables

and you only have to do this once, not repeatedly,

Exporting to Excel and doing a global replace might be the quickest.

Heretical, I know.

Richard

Contributor
Posts: 29

Re: Find and replace for all numeric variables

Im afraid, this will be a weekly task Smiley Happy

Respected Advisor
Posts: 3,902

Re: Find and replace for all numeric variables

Some code like below should do.

data have;
  length char_var $3;
  array nums {30} 8.;

  do obs=1 to 100;
    char_var=put(obs,3.);
    do ind=1 to dim(nums);
      nums[ind]=ceil(ranuni(1)*10);
    end;
    output;
  end;
run;

/* for all numeric variables */
data want_all_nums;
  set have;
  array nums _numeric_;

  do over nums;
    if nums=9 then
      nums=12345;
  end;
run;

/* for almost all numeric variables */
proc sql noprint;
  select name into :varlist separated by ' '

  from dictionary.columns
  where libname='WORK' and memname='HAVE' and upcase(type)='NUM'
        and upcase(name) not in ('NUMS1','NUMS5')
  ;
quit;

data want_almost_all_nums;
  set have;
  array nums &varlist;

  do over nums;
    if nums=9 then
      nums=12345;
  end;
run;

Occasional Contributor
Posts: 8

Re: Find and replace for all numeric variables

I had a similar challenge where I needed to ensure that any missing value in any of the numeric variables had to be changed to 0. The code is already supplied by Patrick above and this is how i used it

data test2;
  set test1;
  array nums _numeric_;

  do over nums;
    if nums = . then
      nums = 0;
  end;
run;

Super User
Super User
Posts: 7,430

Re: Find and replace for all numeric variables

Alternative to actually replacing a value, you could setup a format using proc format.  So, say I want value=3 to be THREE and value 5 to be FIVE, but 1,2,4 to be the number:

proc format;

     value myformat

          3 = "THREE"

          5 = "FIVE";

run;

/* Some test data */

data have;

     a = 1; b=2; c=3; d=4; e=5; output;

     a=5; b=3; c=5; d=3; e=1; output;

run;

/* Apply the format */

data want;

  set have;

  format a b c d e myformat.;

run;

Ask a Question
Discussion stats
  • 5 replies
  • 2045 views
  • 6 likes
  • 5 in conversation