topic Re: Trouble reading the last variable from a colon delimited in SAS Enterprise Guide

Trouble reading the last variable from a colon delimited

sleretrano — Fri, 28 Oct 2016 12:14:54 GMT

I'm trying to read from a UNIX file whose rows look like this:

42000001251100:137224761:123272858:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:DW7 :P:CP :+002296660:+00000005300:+00000598760:+00000132500:+00000132500:+00000000000:+00000250000:  :00010101:ENC:20120314:ENC:20120215:0:502:00010101:20100914:20111027:+00000007300:119

Notice that after the last field there isn't a ':' delimiter. I checked in a text editor and there's no "end of line" (i.e. CRLF, CR, LF).

When I read from the file, everything works fine except for the last variable which always comes out empty.

It's not a lrecl issue, I've checked.

I managed to fix this by doing the following:

lrecl=500000

DLM=':'

missover DSD;

Input Var1-Var35 
        @350 Var36;

As the last variable (the thirty sixth one) starts at the position 350.

However I was looking for a more elegant solution.

Any ideas?

This a follow up from this question: https://communities.sas.com/t5/SAS-Enterprise-Guide/Reading-from-Unix/td-p/307696

Re: Trouble reading the last variable from a colon delimited

RW9 — Fri, 28 Oct 2016 13:09:52 GMT

Firstly, that is not a semicolon delimited file, it is a colon delimited file. Next, I ran this code:

data want;
  infile "s:\temp\rob\a.txt" lrecl=500000 dlm=':' missover dsd;
  input Var1-Var36;
run;

And it imported that row you have given quite simply. There are problems however as some of the variables have character data, those are not read in as the input defaults to numeric. Also, if it is from Unix you might need to put the option termstr=, per the guidance given here:

http://support.sas.com/kb/14/178.html

Re: Trouble reading the last variable from a colon delimited

data_null__ — Fri, 28 Oct 2016 13:12:03 GMT

Do you get a message that looks like this.

NOTE: Invalid data for var36 in line 1 350-353.
RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                      
1         42000001251100:137224761:123272858:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:
     101  +00000000000:+00000000000:+00000000000:+00000000000:+00000000000:DW7 :P:CP :+002296660:+00000005300:
     201  +00000598760:+00000132500:+00000132500:+00000000000:+00000250000:  :00010101:ENC:20120314:ENC:201202

     301  15:0:502:00010101:20100914:20111027:+00000007300:119. 353
    ZONE  33333333333333333333333333333333333323333333333333330
    NUMR  15A0A502A00010101A20100914A20111027AB00000007300A119D
var1=4.2000001E13 var2=137224761 var3=123272858 var4=0 var5=0 var6=0 var7=0 var8=0 var9=0 var10=0 var11=0 var12=0 var13=0 var14=DW7
var15=P var16=CP var17=2296660 var18=5300 var19=598760 var20=132500 var21=132500 var22=0 var23=250000 var24=  var25=10101 var26=ENC
var27=20120314 var28=ENC var29=20120215 var30=0 var31=502 var32=10101 var33=20100914 var34=20111027 var35=7300 var36=. _ERROR_=1
_N_=1

Re: Trouble reading the last variable from a colon delimited

sleretrano — Fri, 28 Oct 2016 13:15:39 GMT

I do! Know how to fix it?

Re: Trouble reading the last variable from a colon delimited

sleretrano — Fri, 28 Oct 2016 13:17:57 GMT

I don't understand why you point out that it's not a semicolon delimited file, what is the relevance of that?
I'm aware that some vars have characters, I can fix those. My issue is with the last variable. It doesn't work for me. I tried termstr=CR, termstr=CRLF and termstr=LF, nothing worked.

Re: Trouble reading the last variable from a colon delimited

data_null__ — Fri, 28 Oct 2016 13:59:03 GMT

What is the last character shown by CHAR ZONE and NUMR. In my example they are .0D displayed vertically.

What is your SAS version? You are using some flavor of UNIX right?

Re: Trouble reading the last variable from a colon delimited

sleretrano — Fri, 28 Oct 2016 15:33:53 GMT

I'm using SAS EG 5.1.
The origin is UNIX, that's correct.
I don't know what you mean with " last character shown by CHAR ZONE and NUMR", can you explain it to me, please?

Re: Trouble reading the last variable from a colon delimited

sleretrano — Fri, 28 Oct 2016 15:35:14 GMT

Nevermind, I had misread your reply. Let me get back to you.

Re: Trouble reading the last variable from a colon delimited

sleretrano — Fri, 28 Oct 2016 15:52:27 GMT

I'm sorry, I actually don't get that message. I did at some point get a similar one, but it's not the same and I don't get the rows with "NUMR" and "ZONE" behind. The similar message I got was because I formated a character variable as a number variable.

Re: Trouble reading the last variable from a colon delimited

data_null__ — Fri, 28 Oct 2016 15:53:52 GMT

@sleretrano wrote:

I don't know what you mean with " last character shown by CHAR ZONE and NUMR", can you explain it to me, please?

You said you received the message similar to mine. I would be helpful if you show the message and the record dump. I was asking what it the last character because in my example I made your input file a DOS file and told SAS it was UNIX so that '0d'x became part of the record not TERMSTR. I'm not saying this is your problem exactly you have not given enough information to determine the problem.

     301  15:0:502:00010101:20100914:20111027:+00000007300:119. 353
    ZONE  33333333333333333333333333333333333323333333333333330
    NUMR  15A0A502A00010101A20100914A20111027AB00000007300A119D

Here we can see that the last character is '0d'x carriage return. When a record with unprintable characters if displayed with the LIST statement (implied for invalid data) SAS prints the characters in the first line I called CHAR and unprintable characters are displayed with a dot. Then under that ZONE and NUMR are the values displayed in HEX vertically. ZONE is the first hex digit and NUMR is the second. This display requires fixed with font to make sense.

Re: Trouble reading the last variable from a colon delimited

Ksharp — Sat, 29 Oct 2016 05:27:57 GMT

Read it as character , Can it work ?

Input Var1-Var35 
        @350 Var36 : $20.;

If it was worked, then your termstr= is not right, try others like termstr=nl.

Or your file is stream file (not have row-end character),then you need other input method like:

Input Var1-Var35 
        @350 Var36 @@;