topic Trapping bad data from EXCEL in SAS Programming

Trapping bad data from EXCEL

deleted_user — Tue, 09 Mar 2010 17:29:32 GMT

We have a batch process that imports a large number of EXCEL workbooks.
Sometimes a field which ought to be numeric contains character data.
This error stops the entire process.
We would like to be able to note/trap the error and continue importing the rest of the workbooks.

How can we achieve this?

Thanks in Advance

Re: Trapping bad data from EXCEL

sbb — Tue, 09 Mar 2010 18:20:21 GMT

Are you using SAS PROC IMPORT or are you using some DATA step or another technique to "import" your data to SAS? You can always use INFILE/INPUT processing, input the field as CHARACTER, test it using an ANYxxxxx type function and execute accordingly as you prescribe in your program.

Scott Barry
SBBWorks, Inc.

Re: Trapping bad data from EXCEL

ChrisNZ — Tue, 09 Mar 2010 21:41:29 GMT

As Scott says, the usual way to do this is to
- read values into temp character variables,
- test the validity of the values,
- convert to numeric and drop temp character variables,
- save.

Re: Trapping bad data from EXCEL

Flip — Wed, 10 Mar 2010 13:05:58 GMT

You need to code error handling routines to trap and reset error codes to let your process continue. I found several papers that my help you for instance
http://www.nesug.org/proceedings/nesug05/ap/ap9.pdf

Google "error handling sas"