data have;
input var $ &;
cards;
23,32
12.32
32 42
23ab
$23
123
1,5
2.6
1,2,3
1,2.4
1.2.5
;
run;
I want to load required data (nearest integers and digits) in want and error data in error.
want:
23
12
123
2
3
error: load below error data (notdigits other than , and .) and , or . appear in more than once
23ab
$23
1,2,3
1,2.4
1.2.5
The fun of data cleaning.
Look at anydigit, anychar etc functions to get you started.
/* test.sas */
data have;
input var $ &;
cards;
23,32
12.32
32 42
23ab
$23
123
1,5
2.6
1,2,3
1,2.4
1.2.5
;
data required error_data (drop=varN);
set have;
cnt = countc(var,'.,'); /* count periods and commas */
if cnt in (0 1) then do; /* may be valid */
tmpC = translate(var,'.',','); /* change comma to period */
tmp = input(tmpC,??best12.); /* can be numeric? */
end;
else
tmp = .; /* missing value means invalid input */
if tmp = . then
output error_data; /* write to error file */
else do;
varN = round(tmp); /* find nearest integer */
output required; /* write to OK file */
end;
drop tmp tmpC cnt;
run;
proc print data=required; title "Nearest integer";run;
proc print data=error_data; title "Contains invalid characters ";run;
***********************
cheers,
Jan
Would it be possible to use same validations for multiple variables as var column in efficient way
Peter's approach with 2 small amendments:
1. use a informat which deals with both ',' and '.'
2. set "errors" to 0 for not cluttering the log
If you want to apply the round() function to multiple variables then use an array and loop over this array.
%let save_errors=%str(errors=%sysfunc(getoption(errors)));
options errors=0;
data error(keep=record) want(drop=record);
infile datalines truncover;
input nums ? numx32.;
if _error_ or missing(_infile_) then
do;
record=_infile_;
output error;
end;
else do;
nums=round(nums,1);
output want;
end;
cards;
23,32
12.32
32 42
23ab
$23
123
1,5
2.6
1,2,3
1,2.4
1.2.5
;
run;
options &save_errors;
if the objective is to control data loading so that records with invalid data go into a rejects pile, and the good stuff is stored separately, then little additional programming is needed, because SAS supports convenient variable lists on INPUT statements.
As with much of "old SAS" not a lot of people seem to remember
input ( a--b) (?) ;
will input all variables starting with A through to variable B (in the order defined to the compiler) with default informats and suppress invalid data messages, but still setting _ERROR_ to 1 if any informat fails to recognise its data..
The style
( var list) (format list and format modifiers)
allows the ? modifier to apply to all these variables.
The data step structure would then work like
data errors( compress=yes) wanted( drop= record ) ;
length A 8 ;
informat var1 informat_for_var1 var2 $informat_for_var2 var3 ..............varZ informat_for_varZ ;
length B $1 record $1000 ;
infile 'where-ever\your_data' truncover {other infile options} ;
input (var1 -- varz) (?) @ ;
record = _infile_ ;
if _error_ then output errors ; else output wanted ;
drop A B ;
_error_ = 0 ;
run ;
although I didn't use A and B they seem to appear in many of my programs where I might want to subset the list of variables to pick out just the numerics or just the character variables, because another variable list that is suppoprted in a data step is
a-numeric-b or a-character-B .
put ( a-numeric-b )( =/) ;
would just PUT the variables between A and B.
And more for fun than thought to be actually used:
proc fcmp outlib=work.funcs.trial;
function str2num(string $);
if prxmatch('/^\d*[,\.]?\d*$/oi',strip(string)) then
do;
num=input(string,numx.);
num=round(num,1);
end;
return (num);
endsub;
run;
options cmplib=work.funcs;
proc format;
invalue testx
other=[str2num()]
;
run;
data error(keep=record) want(drop=record);
infile datalines truncover;
input nums testx16.;
if missing(nums) then
do;
record=_infile_;
output error;
end;
else do;
output want;
end;
cards;
23,32
12.32
32 42
23ab
$23
123
1,5
2.6
1,2,3
1,2.4
1.2.5
;
run;
use _ERROR_ to detect invalid data, to write to ERRORS table
63 data have want errors;
64 infile datalines truncover ;
65 input @1 chars $char20. @1 nums ? ;
66 if _error_ then output errors ; else output want ;
67 output have ;
68 _error_=0 ;
69 datalines;
NOTE: The data set WORK.HAVE has 12 observations and 2 variables.
NOTE: The data set WORK.WANT has 5 observations and 2 variables.
NOTE: The data set WORK.ERRORS has 7 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.08 seconds
cpu time 0.06 seconds
82 ;
83 data _null_ ;
84 do while( not end1) ;
85 set want end= end1 ;
86 put nums= ;
87 end ;
88 do while( not end2) ;
89 set errors end= end2 ;
90 put chars= ;
91 end ;
92 stop;
93 run;
nums=12.32
nums=32
nums=123
nums=2.6
nums=.
chars=23,32
chars=23ab
chars=$23
chars=1,5
chars=1,2,3
chars=1,2.4
chars=1.2.5
NOTE: There were 5 observations read from the data set WORK.WANT.
NOTE: There were 7 observations read from the data set WORK.ERRORS.
NOTE: DATA statement used (Total process time):
real time 0.14 seconds
cpu time 0.00 seconds
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.