SAS: Specify newline character when input a text file

Question

I'm quite new to SAS and have a very simple problem. I have a text file which is saved like this:

123,123,345,457,4.55~123,123,345,457,4.55~123,123,345,457,4.55

So all the data is written in a single line. The ~ character denotes the newline character. My final goal is to load the text file into sas and create a SAS dataset which should look like this:

V1  V2  V3  V4  V5 
123 123 345 457 4.55
123 123 345 457 4.55
123 123 345 457 4.55

So ',' is the delimiter and '~' is the new line character. How can I achieve this?

Thank you very much for your response.

Kind Regards Consti

score 2 · Accepted Answer · answered Dec 14 '17 at 17:45

Just tell SAS to use both characters as delimiters and add @@ to the input statement to prevent it from going to a new line.

data want ;
  infile cards dsd dlm=',~';
  input v1-v5 @@ ;
cards;
123,123,345,457,4.55~123,123,345,457,4.55~123,123,345,457,4.55
;;;;

Result

Obs     v1     v2     v3     v4     v5
 1     123    123    345    457    4.55
 2     123    123    345    457    4.55
 3     123    123    345    457    4.55

If you are reading from a file then you might also be able to use the RECFM=N option on the INFILE statement instead of the @@ on the INPUT statement, although if the one line actually has LF or CR/LF at the end then you might want to include them in the delimiter list also.

score 1 · Answer 2 · answered Dec 14 '17 at 19:52

Tom's answer is correct for files that are regular and you don't have issues with inconsistent rows.

If you do need to do exactly what you say though, it's possible; you'd convert ~ to a newline through a pre-processing step. Here's one way to do that.

First, in a data step go through the file with dlm of ~; input the fields until you run to the end of the line, and for each field, output it to a temp file (so now the line has just one data row on it).

Now you have a temp file you can read in like normal, with no ~ characters in it.

You could do this in a number of other ways, literally find/replace ~ with '0D0A'x or whatever your preferred EOL charater is for example (easier/faster to do in another language probably, if you have this in unix and have access to perl for example or even using awk/etc. you could do this probably more easily than in SAS).

filename test_in "c:\temp\test_dlm.txt";
filename test_t temp;

data _null_;
  infile test_in dlm='~';
  file test_t;
  length test_field $32767;
  do _n_= 1 by 1 until (_n_ > countc(_infile_,'~'));
    input
      test_field :$32767. @@;
    putlog test_field;
    put test_field $;
  end;
  stop;
run;

data want;
  infile test_t dlm=',';
  input v1 v2 v3 v4 v5;
run;

SAS: Specify newline character when input a text file

2 Answers2