DATA Step, Macro, Functions and more

Replace Numeric variables values

Reply
Frequent Learner
Posts: 1

Replace Numeric variables values

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;

 

Super User
Posts: 10,255

Re: Replace Numeric variables values

'-' 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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 161

Re: Replace Numeric variables values

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

--------------
blog: papersandprograms.com
Super Contributor
Posts: 312

Re: Replace Numeric variables values

[ Edited ]

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;
Super User
Posts: 10,255

Re: Replace Numeric variables values

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

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,542

Re: Replace Numeric variables values

Posted in reply to KurtBremser

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.

 

Ask a Question
Discussion stats
  • 5 replies
  • 97 views
  • 4 likes
  • 5 in conversation