topic Import a TXT file with data split by a symbol in SAS Programming

Import a TXT file with data split by a symbol

Jarvin99 — Mon, 09 May 2022 08:59:12 GMT

Question 1:

I have an attached 'try' txt file, which is split by the symbol |. As a rookie, I want to import it nicely into the SAS but not sure how to do it.

Each row should have 21 columns. Hence, some rows may have missing values in some columns.

Question 2:

After I import the txt file successfully, how do I add a header, say for 21 columns, to this new SAS data? The header I wanna add is also attached here with the name "header".

Thank you so much.

Re: Import a TXT file with data split by a symbol

Kurt_Bremser — Mon, 09 May 2022 09:32:52 GMT

Use the header line to write your code, and use informats according to the data.

But, in a professional environment, you should accept files only with proper documentation, so you do not have to make guesses about variable attributes.

Anyway, this seems to read your file correctly:

data want;
infile "~/try.txt" dlm="|" dsd truncover;
input
  CMTE_ID :$10.
  AMNDT_IND :$1.
  RPT_TP :$2.
  TRANSACTION_PGI :$5.
  IMAGE_NUM :$20.
  TRANSACTION_TP :$3.
  ENTITY_TP :$3.
  NAME :$50.
  CITY :$50.
  STATE :$2.
  ZIP_CODE :$10.
  EMPLOYER :$50.
  OCCUPATION :$30.
  TRANSACTION_DT :mmddyy8.
  TRANSACTION_AMT
  OTHER_ID :$8.
  TRAN_ID :$20.
  FILE_NUM :$8.
  MEMO_CD :$1.
  MEMO_TEXT :$100.
  SUB_ID :$20.
;
format TRANSACTION_DT yymmdd10.;
run;

Re: Import a TXT file with data split by a symbol

Jarvin99 — Mon, 09 May 2022 10:19:37 GMT

Thank you for your reminder. Here is a brief manual from the database. I have changed most of the character formats, but I am not sure how to change the numeric format, i.e., TRANSACTION_AMT from its default length of 8 to 14 with 2 decimals behind.

I know it may be simple but thanks for any help given.

Re: Import a TXT file with data split by a symbol

AMSAS — Mon, 09 May 2022 12:41:35 GMT

Note Length and Format are not the same, you really don't want to mess with the length of numeric variables
see Length and Precision of Variables

A format is how SAS displays data
see About Formats

Re: Import a TXT file with data split by a symbol

Kurt_Bremser — Mon, 09 May 2022 14:05:37 GMT

You do not want to change the length of a numeric variable, as that concerns the number of bytes used to store the numbers. What you want to change is the display format for the numeric values.

To reliably display up to 14 digits plus 2 fractional digits, use a format of 18.2.

18 = 14 + 1 (dot) + 1 (sign) + 2 (fractional digits)

In the code I gave you, extend the FORMAT statement:

format
  TRANSACTION_DT yymmdd10.
  TRANSACTION_AMT 18.2
;

Note that a number with more than 15 overall decimal digits will have imprecisions in the last digits, because of the limits of 8-byte real storage.