Delimited file

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

Delimited file

Hi, I'm trying to import a txt file


The exactly original txt file is below:


0001 AAA      010493991500002   30/07/2015      22:23:34               5,50        FAR  M     002002    000641

0001 AAA      010277850230001   30/07/2015      19:36:04             22,00        FAR  M     002002    039393

0001 AAA      WY732223         03/05/2015       13:30:36              29,90        FAR  M     002002    078008

0001 AAA      78764472           01/05/2015       00:26:42              76,25        FAR  M     002002    006044

But when I run my data step within infile statement delimited by "     " (tab) or (space) it stays like this:

tbaa.png

As you guys can see sometimes it is delimited by 1 space sometimes more.

And the third column will contain the number like this 010493991500002 and WY732223 at the same place.

How can i solve this ?

Tks,

Rodrigo Elias

Attachment

Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Regular Contributor
Posts: 212

Re: Delimited file

Guys i solved this issue by importing this file in one column and split them.

And after this i use multiple IF conditions to check if spaces are greater or not.

IF STRIP(SUBSTR(ENTIRE_ROW,22, 2)   = "" THEN DO

     Here create all necessary columns when condition is true. With all proper formating to this match.

IF STRIP(SUBSTR(ENTIRE_ROW,22, 2) ne "" THEN DO

     Here create all necessary columns when condition is true. With all proper formating to this match.

This worked very well.

Thank you all

View solution in original post


All Replies
Super User
Posts: 19,080

Re: Delimited file

Please post your code. If you can include data in a text file that's also great.

Super User
Posts: 11,118

Re: Delimited file

It helps to show the code you attempted to address specific issues. For instance, did you do anything else in your data step? It would be very difficult to get a value of POS for the data you posted by only reading data.

Your third column will have to be a character variable as the number of characters you display exceed the precision that SAS will store numeric and to allow values of WY732223.

I don't see anything in the posted data that should be a problem with the correct informat specified for the variables.

The date should be read with ddmmyy10. , the time Stimer8. , the values such as 5,50 I would try Best12. ,

the rest look like they should be $ with sufficient characters indicated to store the longest value expected in the data. It might help to have the INFORMAT statement(s) prior to the input.

Regular Contributor
Posts: 212

Re: Delimited file

DATA STG_TBAA;

    LENGTH

        Arq          $ 4

        Num           $ 16

        Canal            $ 8

        Place     $ 15

        Data               8

        Hora               8

        Valor              8

        Tipo             $ 6 ;

    FORMAT

        Arq          $CHAR4.

        Num           $CHAR16.

        Canal            $CHAR8.

        Place $CHAR14.

        Data             DDMMYY10.

        Hora             TIME8.

        Valor            BEST8.

        Tipo             $CHAR6. ;

    INFORMAT

       Arq          $CHAR4.

        Num           $CHAR16.

        Canal            $CHAR8.

        Place  $CHAR15.

        Data             DDMMYY10.

        Hora             TIME11.

        Valor            BEST8.

        Tipo             $CHAR6. ;

    INFILE "/home/re43526/quickview/TBAA/tbaa*.TXT"

  FIRSTOBS=2

        DLM=';'

        MISSOVER

        DSD ;

    INPUT

        Arq          : $CHAR4.

        Num           : $CHAR16.

        Canal            : $CHAR8.

        Place : $CHAR15.

        Data             : ?? DDMMYY10.

        Hora             : ?? TIME8.

        Valor            : COMMAX8.2

        Tipo             : $CHAR6. ;

  DROP CANAL;

RUN;

Sorry file i can't there is some customers data in it.

Super User
Posts: 19,080

Re: Delimited file

Make up data that's the same, it only needs to be a few lines.

Super User
Posts: 11,118

Re: Delimited file

On issue with using the format on the input is that it can force reading all of the columns even when the variable is shorter hence encountering the delimiter. Then the count of delimiters doesn't match the layout. with the Length and Format statements prior to the INPUT you should not need them on the input statement.

Make sure the lengths match up with the informats.

Regular Contributor
Posts: 212

Re: Delimited file

I posted

Super User
Posts: 5,361

Re: Delimited file

Why does your INFILE statement specify that semicolons are the delimiter, when your data contains no semicolons?

Super User
Super User
Posts: 6,843

Re: Delimited file

The file you posted looks like a normal text file. It is not a "delimited" file.  It does not have tabs or commas or semi-colons between the fields, it just has spaces between the fields.

The order of the fields does not look the same.  The date and time are the 4th and 5th values on the lines not the 5th and 6th.   There appears to be 10 fields instead of 8.

Do you know if any of the rows have missing values for some of the variables?  If so then you will have a problem unless the missing values are represented by a period or some other text.

If there are just spaces between the fields then just use normal list mode input.

data stg_tbaa;

  length var1 $4 var2 $3 var3 $16 data 8 hora 8 valor 8 var7 $3 var8 $1 var9 $6 var10 $6 ;

  format data ddmmyy10.  hora time8.  ;

  informat data ddmmyy10.  hora time11.  valor commax8. ;

  infile cards truncover ;

  input var1 -- var10 ;

cards;

0001 AAA      010493991500002   30/07/2015      22:23:34               5,50        FAR  M     002002    000641

0001 AAA      010277850230001   30/07/2015      19:36:04             22,00        FAR  M     002002    039393

0001 AAA      WY732223     03/05/2015       13:30:36              29,90        FAR  M     002002    078008

0001 AAA      78764472     01/05/2015       00:26:42              76,25        FAR  M     002002    006044

;;;;

proc print; run;

Regular Contributor
Posts: 212

Re: Delimited file

The truth is i don't know bacuse my this files is very extensive.

But how do i do using infile statement, with external file ?

Super User
Super User
Posts: 6,843

Re: Delimited file

I am not sure I understand the question. If you want the syntax of the INFILE statement look at the on-line manual.

It is pretty simple:

INFILE 'filename' <options>;

If you want to examine your file I recommend running this simple program to dump the first few lines to the SAS log.  If there are non-printable characters in the line the LIST command will display a period for them and print the HEX codes for all of the characters in two rows underneath the line.  The LIST command will also print the length of the line so you can tell if your file has spaces padded on the end of the lines.

data _null_;

   infile 'my file name' obs=10 ;

   input;

   list;

run;

Regular Contributor
Posts: 212

Re: Delimited file

Actualy it is a raw file that contains loads of missing and strange lines.

I think the best way to do this is to print all in one column and split it into all necessary columns.

This occurred because they changed the layout.

Thank you guys anyway.

Super User
Super User
Posts: 7,711

Re: Delimited file

So your importing a file.  You will then of course followed good procedure and drawn up a transfer document which details schedule of transfers, file format, file structure, file contents, method of transfer etc.  This would then have been signed off by all parties involved in the data, hence any changes to the file would be invalid per agreement/contract. 

Of course, if nothing above has happened, then you are blowing in the wind, that data may change at any point and you have no come back.  I know which boat I would rather be in Smiley Happy

Super User
Posts: 9,867

Re: Delimited file

Maybe your delimiter is not BLANK or TAB character . Just copy it into DLM='   ' ;  <--copy your delimiter  into it .(I used to do it ,very convenient .)

OR use $HEX. to find out which character it exactly is . and type  DLM='04'x  ;

Xia Keshan

Solution
‎09-25-2015 06:23 AM
Regular Contributor
Posts: 212

Re: Delimited file

Guys i solved this issue by importing this file in one column and split them.

And after this i use multiple IF conditions to check if spaces are greater or not.

IF STRIP(SUBSTR(ENTIRE_ROW,22, 2)   = "" THEN DO

     Here create all necessary columns when condition is true. With all proper formating to this match.

IF STRIP(SUBSTR(ENTIRE_ROW,22, 2) ne "" THEN DO

     Here create all necessary columns when condition is true. With all proper formating to this match.

This worked very well.

Thank you all

🔒 This topic is solved and locked.

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

Discussion stats
  • 18 replies
  • 479 views
  • 6 likes
  • 7 in conversation