topic Re: Reading Tab delimited file with non standard data in SAS Programming

Input Files usage in SAS

Data_User — Thu, 22 Jun 2017 23:03:41 GMT

Hi,

filename inp 'C:\Users\DATA3.txt';

data want;
infile inp dlm='09'x pad missover truncover firstobs=2;
input

Field1 $
Field2
Field3 $
Field4 $
Field5
Field6 $
Field7
Field8
Field9;
run;

proc print;run;

Re: Reading Tab delimited file with non standard data

Shmuel — Sat, 17 Jun 2017 20:42:13 GMT

Try with:

infile inp dlm='09'x dsd truncover firstobs=2;

Re: Reading Tab delimited file with non standard data

SuzanneDorinski — Sat, 17 Jun 2017 21:16:02 GMT

I used PROC IMPORT to read your sample file. The log showed 10 fields, rather than 9.

I opened up the file in Notepad++ with the "show tab" option. In the screen shot below, the tabs are the orange arrows.

I copied the code in the log that PROC IMPORT generated, then modified it.

FILENAME inp "/folders/myfolders/SAS Communities/DATA3.txt";

data WORK.WANT;
	%let _EFIERR_ = 0;

	/* set the ERROR detection macro variable */
	infile INP delimiter='09'x MISSOVER DSD firstobs=2;
	input field1 $
          field2 
          field3 $
          field4 $
          field5 
          field6 $
          field7 $
          field8 
          field9 
          field10;

	if _ERROR_ then
		call symputx('_EFIERR_', 1);

	/* set ERROR detection macro variable */
run;

proc print data=want;
run;

There's a note in the log about invalid data for field8 line 254. The numeric value has quotes around it, so that value is missing in the WANT data set.

Re: Reading Tab delimited file with non standard data

Data_User — Sat, 17 Jun 2017 21:18:44 GMT

Hi,

I used the above option, but it's not working. Getting the below error.

NOTE: Invalid data for Field7 in line 2 30-32.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9--

Thanks

Re: Reading Tab delimited file with non standard data

Data_User — Sat, 17 Jun 2017 21:32:20 GMT

Wow! This is awesome @SuzanneDorinski. It's working. Thanks a lot for looking into this.

But the only problem I have noticed is if you see the input file the value for Material Variable is "10000000000000" but in SAS it's reading as "10000000". Can we fix this by any chance?

Thanks

Re: Reading Tab delimited file with non standard data

SuzanneDorinski — Sat, 17 Jun 2017 21:41:55 GMT

Looks like field3 needs to have the length set as 14.

data WORK.WANT;

    length field3 $ 14;
    
	%let _EFIERR_ = 0;

	/* set the ERROR detection macro variable */
	infile INP delimiter='09'x MISSOVER DSD firstobs=2;
	input field1 $
          field2 
          field3 $
          field4 $
          field5 
          field6 $
          field7 $
          field8 
          field9 
          field10;

	if _ERROR_ then
		call symputx('_EFIERR_', 1);

	/* set ERROR detection macro variable */
run;

Re: Reading Tab delimited file with non standard data

Data_User — Sat, 17 Jun 2017 21:46:15 GMT

Fentastic! It's working @SuzanneDorinski. Appreciate your help on this!. Thanks

Re: Reading Tab delimited file with non standard data

Tom — Sat, 17 Jun 2017 22:32:09 GMT

In your data file your header line has 16 fields and the data lines have 10 fields, but the 6th one is totally empty.

So here is a way to read that and ignore the empty one. Also some of the values have commas, which you can handle with the COMMA informat.

It is really much easier to define the variables first rather than attempting to define them via the INPUT statement. If some of the variables need informats to read the source file properly then use an INFORMAT statement. In this case we can just read all of the numbers using the COMMA. informat.

To skip the empty 6th column you can first read the first 6 columns into FIELD1 to 6 and then read the next four columns into FIELD6 to 9.

data want;
  infile "&path\DATA3.txt" dsd dlm='09'x truncover firstobs=2;
  length field1 $3 field2 8 field3 $14 field4 $2 field5 8 field6 $3 field7 8 field8 8 field9 8 ;
  informat _numeric_ comma.;
  input field1-field6 field6-field9;
run;

Re: Reading Tab delimited file with non standard data

Data_User — Sat, 17 Jun 2017 22:43:27 GMT

Hi Tom, This is awesome too! It's perfectly working. Your contribution is much appreciated. Thanks