base sas

[sunilreddy]

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

[Reeza]

The fun of data cleaning.

Look at anydigit, anychar etc functions to get you started.

[jwsquillace]

/* 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

[sunilreddy]

Would it be possible to use same validations  for multiple variables as var column in efficient way

[Patrick]

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;

[Peter_C]

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.

[Patrick]

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;

[Peter_C]

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