DATA Step, Macro, Functions and more

Input Files usage in SAS

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

Input Files usage in SAS

[ Edited ]

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;


Accepted Solutions
Solution
‎06-17-2017 05:33 PM
Frequent Contributor
Posts: 89

Re: Reading Tab delimited file with non standard data

Posted in reply to Data_User

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.

 

Notepad++ can show tabs in tab delimited file.jpg

 

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.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,553

Re: Reading Tab delimited file with non standard data

Posted in reply to Data_User

Try with:

  

infile inp dlm='09'x dsd truncover firstobs=2;
Contributor
Posts: 43

Re: Reading Tab delimited file with non standard data

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

Solution
‎06-17-2017 05:33 PM
Frequent Contributor
Posts: 89

Re: Reading Tab delimited file with non standard data

Posted in reply to Data_User

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.

 

Notepad++ can show tabs in tab delimited file.jpg

 

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.

Contributor
Posts: 43

Re: Reading Tab delimited file with non standard data

Posted in reply to SuzanneDorinski

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

 
Frequent Contributor
Posts: 89

Re: Reading Tab delimited file with non standard data

Posted in reply to Data_User

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;
Contributor
Posts: 43

Re: Reading Tab delimited file with non standard data

Posted in reply to SuzanneDorinski

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

Super User
Super User
Posts: 7,039

Re: Reading Tab delimited file with non standard data

[ Edited ]
Posted in reply to Data_User

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;

 

Contributor
Posts: 43

Re: Reading Tab delimited file with non standard data

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 326 views
  • 3 likes
  • 4 in conversation