topic Re: Replace value in the whole table in SAS Enterprise Guide

Replace value in the whole table

Dataminer — Wed, 30 Oct 2013 07:34:53 GMT

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.

Re: Replace value in the whole table

Jagadishkatam — Wed, 30 Oct 2013 07:39:12 GMT

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

Re: Replace value in the whole table

HongqiuGu — Wed, 30 Oct 2013 07:59:17 GMT

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;

Re: Replace value in the whole table

Dataminer — Wed, 30 Oct 2013 08:26:17 GMT

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?

Re: Replace value in the whole table

HongqiuGu — Wed, 30 Oct 2013 08:34:27 GMT

pls try my updated code.

Re: Replace value in the whole table

Dataminer — Wed, 30 Oct 2013 09:26:10 GMT

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

Re: Replace value in the whole table

Dataminer — Thu, 09 Oct 2014 07:27:58 GMT

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

Re: Replace value in the whole table

Erick1215 — Mon, 17 Aug 2020 20:24:00 GMT

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;

Re: Replace value in the whole table

Tom — Mon, 17 Aug 2020 22:02:34 GMT

@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.