Import a delimiter file : |

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

Import a delimiter file : |

I have been trying to import a .dat file :

below is the code i am using :

DATA WORK.smt_rr;

    LENGTH

        F1                 8

        F2                 8

        F3               $ 14

        F4               $ 4

        F5               $ 29

        F6               $ 240

        F7               $ 7

        F8               $ 16

        F9                 8

        F10              $ 49

        F11                8

        F12                8

        F13                8

        F14                8

        F15                8

        F16              $ 26 ;

    FORMAT

        F1               BEST24.

        F2               DATETIME18.

        F3               $CHAR14.

        F4               $CHAR4.

        F5               $CHAR29.

        F6               $CHAR240.

        F7               $CHAR7.

        F8               $CHAR16.

        F9               DATETIME18.

        F10              $CHAR49.

        F11              BEST13.

        F12              BEST1.

        F13              BEST2.

        F14              BEST7.

        F15              BEST2.

        F16              $CHAR26. ;

    INFORMAT

        F1               BEST24.

        F2               DATETIME18.

        F3               $CHAR14.

        F4               $CHAR4.

        F5               $CHAR29.

        F6               $CHAR240.

        F7               $CHAR7.

        F8               $CHAR16.

        F9               DATETIME18.

        F10              $CHAR49.

        F11              BEST13.

        F12              BEST1.

        F13              BEST2.

        F14              BEST7.

        F15              BEST2.

        F16              $CHAR26. ;

    INFILE 'R:\14072.dat'

ENCODING="WLATIN1"

        TERMSTR=LF

        DLM='|'

        MISSOVER

        DSD ;

    INPUT

        F1               : ?? BEST24.

        F2               : ?? ANYDTDTM19.

        F3               : $CHAR14.

        F4               : $CHAR4.

        F5               : $CHAR29.

        F6               : $CHAR240.

        F7               : $CHAR7.

        F8               : $CHAR16.

        F9               : ?? ANYDTDTM19.

        F10              : $CHAR49.

        F11              : ?? BEST13.

        F12              : ?? BEST1.

        F13              : ?? BEST2.

        F14              : ?? COMMA7.

        F15              : ?? BEST2.

        F16              : $CHAR26. ;

RUN;

however i am unable to get the correct data imported.

output is like this..  please see the image  ( i can only see the data uptill variable F6,after that no date is visible.from F1 to F6 everything is perfect)

could not figureout what is wrong with the code,please assist


image.jpg

Accepted Solutions
Solution
‎07-25-2014 02:18 AM
Super User
Super User
Posts: 6,499

Re: Import a delimiter file : |

Did you check what is actually in your file? I suspect that the problem at the 6th column is that you have exceeded the LRECL that SAS is using to read the text file. Until version 9.4 the default LRECL was 256, which is too short based on your combined variables lengths.

Or perhaps it has embedded LF.  Perhaps your TERMSTR is really CRLF (as it would be on a file created on Windows).

Also do not use MISSOVER. Use TRUNCOVER to avoid possibility of the INPUT statement discarding data.

INFILE 'R:\14072.dat' ENCODING='WLATIN1' DSD DLM='|' LRECL=3000 TRUNCOVER ;


Note that you cannot read a 24 digit integer into a numeric field in SAS.  8 Byte IEEE floating point representation cannot store more than 17 decimal digits with exact precision.  So read F1 as a character string.

Also you can vastly simplify your code.  For example, since you have a LENGTH statement you do not need to specify an INFORMAT or a FORMAT or include a format in the INPUT statement for most of the variables.  Do you really want to preserve leading spaces in these character fields?  You really only need INFORMAT for F2, F9 and F14.  And once you have set the INFORMAT you do not need to also include a format in the INPUT statement, just list the variable names.  Also note that there is "feature" of the DATETIME format that it needs you to allow at least 19 characters if you want it to include four digit years.

FORMAT

  F2  DATETIME19.

  F9  DATETIME19.

;

INFORMAT

  F2  DATETIME18.

  F9  DATETIME18.

  F14 COMMA7.

;

INPUT F1 - F16 ;

View solution in original post


All Replies
Super Contributor
Posts: 297

Re: Import a delimiter file : |

Can you please provide a sample of the data you want to import?

Solution
‎07-25-2014 02:18 AM
Super User
Super User
Posts: 6,499

Re: Import a delimiter file : |

Did you check what is actually in your file? I suspect that the problem at the 6th column is that you have exceeded the LRECL that SAS is using to read the text file. Until version 9.4 the default LRECL was 256, which is too short based on your combined variables lengths.

Or perhaps it has embedded LF.  Perhaps your TERMSTR is really CRLF (as it would be on a file created on Windows).

Also do not use MISSOVER. Use TRUNCOVER to avoid possibility of the INPUT statement discarding data.

INFILE 'R:\14072.dat' ENCODING='WLATIN1' DSD DLM='|' LRECL=3000 TRUNCOVER ;


Note that you cannot read a 24 digit integer into a numeric field in SAS.  8 Byte IEEE floating point representation cannot store more than 17 decimal digits with exact precision.  So read F1 as a character string.

Also you can vastly simplify your code.  For example, since you have a LENGTH statement you do not need to specify an INFORMAT or a FORMAT or include a format in the INPUT statement for most of the variables.  Do you really want to preserve leading spaces in these character fields?  You really only need INFORMAT for F2, F9 and F14.  And once you have set the INFORMAT you do not need to also include a format in the INPUT statement, just list the variable names.  Also note that there is "feature" of the DATETIME format that it needs you to allow at least 19 characters if you want it to include four digit years.

FORMAT

  F2  DATETIME19.

  F9  DATETIME19.

;

INFORMAT

  F2  DATETIME18.

  F9  DATETIME18.

  F14 COMMA7.

;

INPUT F1 - F16 ;

Contributor
Posts: 44

Re: Import a delimiter file : |

Thanks Tom... it worked...

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 359 views
  • 0 likes
  • 3 in conversation