topic Re: Identifying data longer than defined in SAS Programming

Identifying data longer than defined

Vimal_Kurup — Mon, 04 Apr 2016 16:16:07 GMT

Hi Team,

I have raw data in .csv file. The length of all the attributes are defined and given by business but they still would like to identify if any data has exceeded already defined length.

How can i do identify ? What if column A has length greater than 11 in Raw File? How can i filter the observation out?

DATA <dataset1> <dataset2>;
INFILE <fileName>
DLM=',' LRECL=32000 DSD MISSOVER FIRSTOBS=2 TERMSTR=CRLF;
INPUT
A :$11.
B
C :$54.
;
IF _ERROR_=1 THEN OUTPUT <dataset1>;
ELSE OUTPUT <dataset2>;
RUN;

Re: Identifying data longer than defined

LinusH — Mon, 04 Apr 2016 16:29:29 GMT

Try make your informats much wider/longer.

But this will make quite an over head for your data import process. A simple matter of filed length should be verified by the sending party.

Re: Identifying data longer than defined

ballardw — Mon, 04 Apr 2016 16:42:10 GMT

I might be tempted to read the data with the specification and then turn Proc import loose with a large guessing rows option.. Look at the resulting variable lengths by position in the dataset using either proc contents, Sashelp.vcolumn or Dictionary.columns. If there are variables in the proc import results that are longer then the defined variables then you something to target.

Re: Identifying data longer than defined

PGStats — Mon, 04 Apr 2016 18:18:14 GMT

DATA <dataset1> <dataset2>;
INFILE <fileName>
DLM=',' LRECL=32000 DSD MISSOVER FIRSTOBS=2 TERMSTR=CRLF;
INPUT
A :$32.
B
C :$54.
;
IF _ERROR_=1 OR length(A) > 11 THEN OUTPUT <dataset1>;
ELSE OUTPUT <dataset2>;
RUN;

Re: Identifying data longer than defined

Ksharp — Tue, 05 Apr 2016 03:47:28 GMT

Check length= option:


INFILE 
DLM=',' LRECL=32000 DSD MISSOVER FIRSTOBS=2 TERMSTR=CRLF
length=len ;
INPUT
A :$11. @;
put len=;

INPUT B @;
put len=;

INPUT C :$54. @;
put len=;

Re: Identifying data longer than defined

Ksharp — Tue, 05 Apr 2016 04:27:21 GMT

Opps.
Check column= option

INFILE 
DLM=',' LRECL=32000 DSD MISSOVER FIRSTOBS=2 TERMSTR=CRLF
column=len ;
INPUT
A :$11. @;
put len=;

INPUT B @;
put len=;

INPUT C :$54. @;
put len=;

Re: Identifying data longer than defined

Kurt_Bremser — Tue, 05 Apr 2016 06:40:00 GMT

How about using a macro?

%macro do_input(varname,len);
input ___&varname :$%eval(&len+100). @;
length &varname $&len;
&varname = ___&varname;
if length(___&varname) > &len
then &varname._err = 1;
else &varname._err = 0;
drop ___&varname;
%mend;

Re: Identifying data longer than defined

Vimal_Kurup — Wed, 13 Apr 2016 17:54:50 GMT

I have employed something very similar. Thanks for your time

Re: Identifying data longer than defined

Vimal_Kurup — Wed, 13 Apr 2016 17:55:52 GMT

Hi Kurt,

Thanks for your time. I have employed something very similar though not through macro.

I like your solution.