topic Re: How do I read multiple space delimited file? in SAS Programming

How do I read multiple space delimited file?

sas_9 — Tue, 16 Apr 2019 15:12:06 GMT

i have text file output like below from linux command and wants to read it into sas.

i tried defining length statement for each variable (and making all values as char) but not reading in fine. i also tried defining informat in length statement for each value (var1 :$10. var2 :$1.) but still not reading it fine.

please help...

-rw-rw-r--. 1 user1 user1 205373 Jan 7 16:33 filename-with-hyphen.txt

-rw-rw-r--. 1 user2 user2 185907 Jan 7 16:33 filename_with_underscore.txt

-rw-rw-r--. 1 xyzabcuser xyzabcuser 131995 Jan 18 15:36 sasdataset.with.dor.sas7bdat

Re: How do I read multiple space delimited file?

Shmuel — Tue, 16 Apr 2019 14:45:25 GMT

Please post your code.

What variables you need and what are your results.

Re: How do I read multiple space delimited file?

ballardw — Tue, 16 Apr 2019 14:47:46 GMT

First thing would be to pipe the output of that command to a text file.

Then copy the first few lines of the text file and paste them into a code box opened using this forum's {I} or "running man" icons. The main message windows will reformat text. So we can't be sure that what you pasted is actually what you are trying to read.

It would also help to show what code you attempted.

OR SAS has functions to request things from the Operating system.

You might investigate the DOPEN, DCLOSE, DOPTNUM, DOPTNAME, DREAD, FINFO, FOPTNAME and FOPTNUM functions which may work better if you have actual users with spaces in their user names.

Re: How do I read multiple space delimited file?

sas_9 — Tue, 16 Apr 2019 15:41:19 GMT

text file structure is same as I mentioned, and this is the full code so far,

data _null_;

length record $500.;

infile "cd /dir-path-to-file; ls -ltr > /temp/test.txt;" pipe;

input;

record= _infile_;

run;

data in;

infile "/temp/test.txt" dsd dlm=' ' firstobs=2;

input

var1 :$10.

var2 :$2.

var3 :$10.

var4 :$10.

var5 :$10.

var6 :$5.

var7 :$2.

var8 :$10.

var9 :$200.

;

run;

Re: How do I read multiple space delimited file?

ballardw — Tue, 16 Apr 2019 15:52:12 GMT

Can you post some of the data from that txt file into a code box as requested.

One reason is because you are using DSD and from the documentation:

When you specify DSD, SAS treats two consecutive delimiters as a missing value

so if your result actually has 2 or more spaces anywhere you are getting missing values.

Second, sometimes the assumption is that the data is space delimited but is actually tab delimited

I notice that you have changed the example data posted as well.

If you get any errors then post the LOG with the code and the errors.

If you are getting unexpected results then we need to see some of the input data and the result.

If you are getting notes about reading past the end of a line you may need the MISSOVER or TRUNCOVER option on the infile statement.

Re: How do I read multiple space delimited file?

sas_9 — Tue, 16 Apr 2019 16:05:52 GMT

so output file is like same as I mentioned in first post. only things is, it has multiple space in between some values.

-rw-rw-r--. 1 user1 user1 205373 Jan 7 16:33 filename-with-hyphen.txt

-rw-rw-r--. 1 user2 user2 185907 Jan 7 16:33 filename_with_underscore.txt

-rw-rw-r--. 1 xyzabcuser xyzabcuser 131995 Jan 18 15:36 sasdataset.with.dor.sas7bdat

when I use above code, output is this,

var1 var2 var3 var4 var5 var6 var7 var8 var9

-rw-rw-r--   1        user1
-rw-rw-r--   1        user2
-rw-rw-r--   1        xyzabcuser                                                                                 xyzabcuser

Re: How do I read multiple space delimited file?

Shmuel — Tue, 16 Apr 2019 16:34:20 GMT

See @ballardw 's post - drop DSD option while readind.

Alternatively you could read your text file and assign values to the variables using SCAN function:

data test;
length record $500.;
infile "cd /dir-path-to-file; ls -ltr > /temp/test.txt;" pipe;
input;
record= _infile_;

length var1 $10 var2 $2 var3 $10 ..... var8 $10 var9 $200;
var1 = scan(record,1);
var2 = scan(record,2)
var3 = scan(record,3);
...
var8 = scan(record,8);
var9 = scan(record,9);

run;

You cab also use array and loop to assign values:

data test;
length record $500.;
infile "cd /dir-path-to-file; ls -ltr > /temp/test.txt;" pipe;
input;
record= _infile_;

length var1 $10 var2 $2 var3 $10 ..... var8 $10 var9 $200;
array varx $ var1 - var9;
do i=1 to dim(varx);
    varx(i) = scan(record,i);
end;

run;

Try it and post your log and results in case of any issue.

Re: How do I read multiple space delimited file?

Shmuel — Tue, 16 Apr 2019 16:38:08 GMT

Default delimiter using scan is a space.
Scan syntax enables defining mor than one delimiter:
varx = scan(record, i, ' '!!'09'x); /* space and tab */

Re: How do I read multiple space delimited file?

sas_9 — Tue, 16 Apr 2019 17:06:32 GMT

thanks Shmuel but this is not returning any value,,,empty sas dataset

data test;
length record $500.;
infile "cd /dir-path-to-file; ls -ltr > /temp/test.txt;" pipe;
input;
record= _infile_;

length var1 $10 var2 $2 var3 $10 ..... var8 $10 var9 $200;
var1 = scan(record,1);
var2 = scan(record,2)
var3 = scan(record,3);
...
var8 = scan(record,8);
var9 = scan(record,9);

run;

Re: How do I read multiple space delimited file?

Shmuel — Tue, 16 Apr 2019 17:21:04 GMT

1) drop the dot in the length statement. It should be:
length record $500 ;
2) In case of any issue post the log.

Re: How do I read multiple space delimited file?

ballardw — Tue, 16 Apr 2019 18:00:00 GMT

Post data and log.

Re: How do I read multiple space delimited file?

Ksharp — Wed, 17 Apr 2019 14:04:44 GMT

Maybe your delimiter is TAB character ,not a blank.

infile ............ dlm='09'x ......

Re: How do I read multiple space delimited file?

Tom — Wed, 17 Apr 2019 14:23:13 GMT

That looks like the output of an ls command on Unix.

Normally you can just use normal list mode input, but I would use formatted input for the filename in case you have any files with spaces in their name.

data want ;
  infile 'myfile' truncover ;
  length var1-var8 $50 filename $256 ;
  input var1-var8 filename $256. ;
run;

Re: How do I read multiple space delimited file?

sas_9 — Wed, 17 Apr 2019 18:21:38 GMT

Thanks to all.

Thank you Tom, yes its output of ls -ltr command in text file.

It worked. so I had to adjust length. what I did is defined specific length for each variable in length statement and then defined formatted input with same length for each variable and it turn out fine.

Have multiple space in between some values (like value1 value2 value3).

data want;

infile "/dir/test.txt" firsobs=2 truncover;

length

var1 $11

var2 $2

var3 $12

filename $256

;

input

var1 $11.

var2 $2.

var3 $12.

filename $256.

;

run;

Re: How do I read multiple space delimited file?

Tom — Wed, 17 Apr 2019 18:34:42 GMT

So that is reading it in fixed columns. Which might not work if some files are owned by users with really long names, or some really large files, such that some of the later values getting shifted to the right. Not sure if those problems might also cause some lines with values written without any spaces between them.

Make sure to test it on as many different types of output as you can get.

Re: How do I read multiple space delimited file?

sas_9 — Wed, 17 Apr 2019 18:50:11 GMT

thanks for input Tom. I am going to validate it against "wc -l test.txt" file within code.