topic Re: base sas in SAS Programming

base sas

sunilreddy — Wed, 03 Apr 2013 17:59:58 GMT

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

Re: base sas

Reeza — Wed, 03 Apr 2013 18:02:24 GMT

The fun of data cleaning.

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

Re: base sas

jwsquillace — Wed, 03 Apr 2013 19:44:23 GMT

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

Re: base sas

Peter_C — Wed, 03 Apr 2013 22:39:33 GMT

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

Re: base sas

sunilreddy — Wed, 03 Apr 2013 22:57:12 GMT

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

Re: base sas

Patrick — Thu, 04 Apr 2013 03:27:00 GMT

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;

Re: base sas

Patrick — Thu, 04 Apr 2013 04:06:23 GMT

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;

Re: base sas

Peter_C — Thu, 04 Apr 2013 21:41:49 GMT

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.