Replace value in the whole table

[Dataminer]

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.

[Jagadishkatam]

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

[HongqiuGu]

Jagadishkatam '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;

[Dataminer]

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?

[HongqiuGu]

pls try my updated code.

[Dataminer]

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

[Dataminer]

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

[Tom]

---

@Dataminer wrote:

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

---

Do you mean Enterprise Guide?  That is a tool for running SAS code.

SAS has two variable types, floating point numbers and fixed length character strings. The format attached to a variable is just instructions for how to display to values as text. 

If you are reading an existing SAS dataset then it will NOT change the type of the variable (or the format attached to it either). 

Did you convert some other file type into a SAS dateset?  If so what type of file?  If you asked EG to import a text file it will guess how to define each variable based on what it sees in that field of the text file.  If you asked EG to import a spreadsheet then any character value in any cell in the column will force SAS to treat that variable as text.

[Erick1215]

data work.example;   #Table Name and where you will save

       set work.example_2;  #The table where you read the data

       array change _numeric_;

                do over change;

                     if change = -9999 then change =. ; # You put the value that you want in this case

        end;

run;