Desktop productivity for business analysts and programmers

Replace value in the whole table

Reply
Occasional Contributor
Posts: 9

Replace value in the whole table

Hello everyone,

I cannot find any option in SAS E-Guide or E-Miner to replace fixed value in the whole table at once, not only in one column. For example, I have table containing 600 variables and some of them (not all) have -9999 among other values representing missing value. I want to replace -9999 with nothing or empty cell. Clear that It is impossible to do for every variable one by one. Is there any code to do it automatically?

ise

Thank you in advance.

Trusted Advisor
Posts: 1,137

Re: Replace value in the whole table

you can use arrays like below

data want;

     set have;

array var(*) var1-var600;

do i = 1 to dim(var);

if var(i)=-9999 then var(i)=.;

end;

run;

Thanks,

Jagadish

Thanks,
Jag
Occasional Contributor
Posts: 16

Re: Replace value in the whole table

's code is OK.

Two kinds of variable may be need to be considered  if you had characteristic var code as '9999'.

data want;

     set have;

array num(*)     _numeric_;

array char(*) $100  _character_;

do i=1 to dim(num);

     if num(i)=9999 then call missing(num(i));

end;

do j=1 to dim(char);     /* corrected* /

if char(j)='9999' then call missing(char(j));

end;

run;

Occasional Contributor
Posts: 9

Re: Replace value in the whole table

Unfortunately I know very little about SAS programming (will definitely take a course in future) and don't quite understand this code. If I write the following in Guide:

data xxx;

set yyy;

array num(*) _numeric_;

array char(*) $100 _character_;

do i=1 to dim(num(i));

     if num(i)=-9999 then call missing(num(i));

end;

do j=1 to dim(char(j));     

  if char(j)=-9999 then call missing(char(j));

end;

run;

I get error:

21         do i=1 to dim(num(i));

ERROR: The DIM, LBOUND, and HBOUND functions require an array name for the first argument.

22              if num(i)=-999997 then call missing(num(i));

23         end;

24         do j=1 to dim(char(j));

ERROR: The DIM, LBOUND, and HBOUND functions require an array name for the first argument.

What should be corrected here? Should num(*) and char(*) be written like that or there have to be variable names inside the brackets?

Occasional Contributor
Posts: 16

Re: Replace value in the whole table

pls try my updated code.

Occasional Contributor
Posts: 9

Re: Replace value in the whole table

Thank you very much for your time! Now code works and I am happy!

Occasional Contributor
Posts: 9

Re: Replace value in the whole table

Hi all

I have a problem with SAS Guide - it reads data formats incorrectly - numeric data (values from 0 to 9) format is character/string.

Can you suggest any code expression which converts variable format with values from 0 to 9 to numeric in the whole table?

Many thanks

Ask a Question
Discussion stats
  • 6 replies
  • 1385 views
  • 6 likes
  • 3 in conversation