topic Re: Reading Pipe delimited txt file with embadded space in SAS Programming

Reading Pipe delimited txt file with embadded space

AmitKB — Thu, 23 Jul 2009 20:15:12 GMT

Hi all,
I have a pipe delimited txt file with embadded space for the address variable. I get the following error.

Data: |455 SE EXCEL ST |

Error: NOTE: Invalid data for AddressLine1 in line 2 79-128

Thanks for all your help.

Regards,
Amit

Re: Reading Pipe delimited txt file with embadded space

sbb — Thu, 23 Jul 2009 20:25:04 GMT

Reply to your post and paste in it your SAS code, preferably the SAS log with the coded executed so the exact error and location is revealed. This suggestion will definitely help narrow the possibilities of your problem source.

Scott Barry
SBBWorks, Inc.

Re: Reading Pipe delimited txt file with embadded space

Doc_Duke — Thu, 23 Jul 2009 20:25:41 GMT

Like sbb, I'll have to say that without the rest of your program and log, we are just guessing. I suspect it is not the space.

Re: Reading Pipe delimited txt file with embadded space

AmitKB — Thu, 23 Jul 2009 20:30:55 GMT

Here is the log code.
Thanks for all your help.

Amit

9479 DATA temp;
9480 INFILE '/test/data.txt'
9481 lrecl=32767 dlm = '|' missover firstobs=2 obs=5 ;
9482 attrib DteOfBirth informat=IS8601DA. format=date9.;
9483 input AppNbr AcctNbr ProductNbr PartyId CustomerNbr DteOfBirth RoleTypNbr AddressLine1 AddressLine2
9484 City State Zip Country SSN Gender DriverIdNbr DriverIdState DriverLicenseExpiration WorkPhone
9485 HomePhone StatementNm TranferAccessMedNbr ReissueDte StatusCd AcctOpenDte CreateDte ;
9486 run;

NOTE: No encoding was specified for the fileref "#LN00099" (for the file "/test/temp.txt"). A byte order mark
in the file indicates that the data is encoded in "utf-16le". This encoding will be used to process the file.
NOTE: The infile '/test/temp.txt' is:
Filename=/'test/temp.txt,
Owner Name=DataTrsfr,Group Name=sasdata,
Access Permission=rw-rw----,
Last Modified=Wed Jul 22 15:04:09 2009,
File Size (bytes)=227300162

NOTE: Invalid data for AddressLine1 in line 2 79-128.
NOTE: Invalid data for City in line 2 181-210.
NOTE: Invalid data for State in line 2 212-214.
NOTE: Invalid data for Gender in line 2 246-246.
NOTE: Invalid data for StatementNm in line 2 314-363.
NOTE: Invalid data for ReissueDte in line 2 385-407.
NOTE: Invalid data for StatusCd in line 2 409-409.
NOTE: Invalid data for AcctOpenDte in line 2 411-433.
NOTE: Invalid data for CreateDte in line 2 435-458.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
2 0 |5291080012009493|230294509 |40065491377|15233204 |1989-09-06 |1|455 SE EXCEL ST
101 | |SHERIDAN
201 |OR |973781962558 |840|541313427|F|0 |0 |0 | |9712
301 411459 |EMILY CROCKER |5291080015467516 |2008-09-03 00:00

401 :00.000|S|2008-09-03 00:00:00.000|2009-07-17 00:00:00.000. 458
ZONE 3332333757333323323323333333323337333323323323333333323330
NUMR A00E000C3C2008D09D03000A00A00E000C2009D07D17000A00A00E000D
DteOfBirth=06SEP1989 AppNbr=0 AcctNbr=5.29108E15 ProductNbr=230294509 PartyId=40065491377 CustomerNbr=15233204 RoleTypNbr=1 AddressLine1=.
AddressLine2=. City=. State=. Zip=973781962558 Country=840 SSN=541313427 Gender=. DriverIdNbr=0 DriverIdState=0 DriverLicenseExpiration=0
WorkPhone=. HomePhone=9712411459 StatementNm=. TranferAccessMedNbr=5.29108E15 ReissueDte=. StatusCd=. AcctOpenDte=. CreateDte=. _ERROR_=1 _N_=1

Re: Reading Pipe delimited txt file with embadded space

lu — Thu, 23 Jul 2009 20:52:39 GMT

AmitKB,you missed formats for input fields.
lu

Re: Reading Pipe delimited txt file with embadded space

AmitKB — Thu, 23 Jul 2009 20:59:32 GMT

Hi Lu,
I was just trying to get a feel of the data before wring the entrie code. However I noticed the embedded space and I dont know how to handle the in pipe delimited file.

Thanks for all your help.

Regards,

Amit

Re: Reading Pipe delimited txt file with embadded space

lu — Thu, 23 Jul 2009 21:16:23 GMT

Hi,AmitKB

Please ask you self all of this fields numeric or character??
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
2 0 |5291080012009493|230294509 |40065491377|15233204 |1989-09-06 |1|455 SE EXCEL ST

1989-09-06 you push into numeric AddressLine1....

Regards,

lu

Re: Reading Pipe delimited txt file with embadded space

AmitKB — Thu, 23 Jul 2009 21:24:03 GMT

Hi Lu,
The log pasting has moved the ruler.

For Adressline1 SAS reads the following:
|455 SE EXCEL ST |

Due to the The embedded space it is not able to read.

Regards,

Amit

Re: Reading Pipe delimited txt file with embadded space

lu — Thu, 23 Jul 2009 21:33:08 GMT

Hi

AddressLine1 is numeric??

DteOfBirth=06SEP1989 AppNbr=0 AcctNbr=5.29108E15 ProductNbr=230294509 PartyId=40065491377 CustomerNbr=15233204 RoleTypNbr=1 AddressLine1=.
AddressLine2=. City=. State=. Zip=973781962558 Country=840 SSN=541313427 Gender=. DriverIdNbr=0 DriverIdState=0

Re: Reading Pipe delimited txt file with embadded space

sbb — Thu, 23 Jul 2009 21:44:05 GMT

Also, in case you have an empty field, you will want to explore coding the DSD INFILE statement parameter. Honestly, what were you expecting to hear back from your original post, especially when no SAS code or diagnostics output was submitted with your post?

You may want to consider "trying to get a feel for the data" by using DATALINES; and coding a small set of instream input or build a 2 to 10 line input file with a data sample -- at least setup your INPUT statement to decode your input file structure and add some PUTLOG _ALL_; commands after your INPUT or near the end of your DATA step, for self-diagnosis and a bit of learning about your data.

Scott Barry
SBBWorks, Inc.

Re: Reading Pipe delimited txt file with embadded space

Peter_C — Thu, 23 Jul 2009 22:00:31 GMT

to convert the pipe delimited content to view in columns, is not complex. Try[pre]
data ;
infile 'your data' dsd dlm='|' lrel= 10000 truncover;
length col1-col30 $20 ;
input col1-col30 ;
run;
proc print ;
run ;[/pre]
That should give an idea of shape and informats required.

Re: Reading Pipe delimited txt file with embadded space

AmitKB — Thu, 23 Jul 2009 23:20:09 GMT

Hi all,
Thanks for all your replies. I will try it tomorrow.

Also I apologies for not providing the log and code in the original post, next time I will be more descriptive.

Regards,

Amit