topic Re: Replace Numeric variables values in SAS Programming

Replace Numeric variables values

Khooper — Mon, 09 Jul 2018 05:47:35 GMT

I am trying to infile a csv file into SAS. However in the CSV, an hypen is used to designate values as missing. Obviously, this causes problems because hyphens cannot be read in as missing. So I believe, I need to replace all hyphens in the numeric variables to periods. I am using SAS BASE 9.4

Here are some of my attempts.

data fixed_total_genders;

set total_genders;

array change _numeric_;

if change eq '-' then call missing(of change);

run;

data fixed_total_genders;

set total_genders;

array(*) _numeric_;

do i=1 to dim(a);

if a(i) = - then a(i) = .;

end;

drop i;

run;

Re: Replace Numeric variables values

Kurt_Bremser — Mon, 09 Jul 2018 06:00:30 GMT

'-' is a string. Numeric variables can never hold a string
Start by correcting this when you read the csv:

data want;
input _numval $;
if _numval = '-'
then numval = .;
else numval = input(_numval,best.);
drop _numval;
cards;
1
2
3
-
4
;
run;

Re: Replace Numeric variables values

pau13rown — Mon, 09 Jul 2018 06:46:17 GMT

newvar=input(tranwrd(var,'-',''),best12.);

Re: Replace Numeric variables values

Scott_Mitchell — Mon, 09 Jul 2018 07:26:04 GMT

Using the Input Statement ?? format modifier allows the value to be set to missing, suppresses the warning message in the log and sets the _ERROR_ automatic variable to 0. The same applies to the Input function.

data want;
input numval ??;
cards;
1
2
3
-
4
;
run;

Re: Replace Numeric variables values

Kurt_Bremser — Mon, 09 Jul 2018 07:45:00 GMT

Which of the methods suggested above you use will depend on your need for checking the validity of the input.

See this example code:

data test;
input _numval :$10.;
if _numval = '-'
then numval1 = .;
else numval1 = input(_numval,best.);
numval2 = input(tranwrd(_numval,'-',''),best12.);
numval3 = input(_numval,?? best.);
cards;
1
2018-07-09
xxxxx
-
.
--
;
run;

Now take a close look at the log of this data step and see which values are converted by the three methods without causing a NOTE and setting _ERROR_.

Re: Replace Numeric variables values

ballardw — Mon, 09 Jul 2018 15:49:22 GMT

And my favorite for such things: a custom informat:

proc format;
invalue nodash
'-'=.
other=[best12.]
;
run;

data work.example;
  input x nodash.;
datalines;
1
12345.666
-
abc
1.2E14
;
run;

Since it is likely that I will process multiple files with the same layout and data conventions informats as either part of the read code or in permanent format catalogs in the current format search path make sense. I know that the - isn't an error but I want other erroneous data to throw errors.