topic Re: Reading from Unix in SAS Enterprise Guide

Reading from Unix

sleretrano — Thu, 27 Oct 2016 17:00:22 GMT

Using EG 5.1.

I'm trying to read from a compressed Unix file using the following code

FILENAME Name pipe "zcat sas-data/SAD/SAD/IFRS 9/BASES/File_name.Z";

Data Test;

infile Name

lrecl=500000

DLM=':'

missover DSD;

label;

Run;

But the output comes out looking like this:

Unziping the file and opening it directly, it looks like this:

42000001251100:137224761:123272858:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:DW7 :P:CP :+002296660:+00000005300:+00000598760:+00000132500:+00000132500:+00000000000:+00000250000: :00010101:ENC:20120314:ENC:20120215:0:502:00010101:20100914:20111027:+00000007300:119

42000002992100:176705643:184314704:+00000188769:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:PW7 :D:CP :+002123330:+00000008000:+00001768510:+00000200000:+00000200000:+00000001389:+00001566513:60:99991231:ENC:20150323:ENC:20150223:0:502:00010101:20141008:20130531:+00000005197:113

42000003721100:134284160:134284151:+00000138210:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:+00000000000:PW7 :D:CP :+002296660:+00000009000:+00001297485:+00000225000:+00000225000:+00000000970:+00000739335:68:20140114:ENC:20140704:RET:20140702:0:502:00010101:20121211:00010101:+00000000000:113

How can I make this work?

Re: Reading from Unix

paulkaefer — Thu, 27 Oct 2016 18:59:14 GMT

I see you have a space in the directory "IFRS 9" in the file path. Can you escape it with a backslash ("IFRS\ 9") and try again?

Re: Reading from Unix

paulkaefer — Thu, 27 Oct 2016 19:00:10 GMT

Also, that's a relative path... can you give it the full path, e.g. /home/user/username/...

Re: Reading from Unix

Kurt_Bremser — Fri, 28 Oct 2016 07:17:41 GMT

I see two issues here

- a relative path that probably won't work, because SAS will go looking for the file from the current directory, which is somewhere in the SAS configuration tree if the default setting has not been changed.

- a blank in the filename

BLANKS IN FILE OR PATH NAMES (on servers) ARE A BAD IDEA (repeat this 1k times)

Try this:

FILENAME Name pipe 'zcat "/sas-data/SAD/SAD/IFRS 9/BASES/File_name.Z"';

If the compressed file is actually somewhere up from your home directory, use $HOME instead of the leading slash.

Re: Reading from Unix

sleretrano — Fri, 28 Oct 2016 08:19:47 GMT

I physically changed the path to remove the space and I tried FILENAME Name pipe 'zcat "/sas-data/SAD/SAD/IFRS9/BASES/File_name.Z"', but it still doesn't work. This particular line of code does run, it also ran in my original try, but when reading from the file it comes out empty. Any ideas?

Re: Reading from Unix

Kurt_Bremser — Fri, 28 Oct 2016 08:23:53 GMT

Then try this:

FILENAME Name pipe 'zcat "/sas-data/SAD/SAD/IFRS9/BASES/File_name.Z" 2>&1';

data _null_;
infile name;
input;
put _infile_;
if _n_ > 10 then stop;
run;

That should at least put error messages from the external command into the log.

Re: Reading from Unix

sleretrano — Fri, 28 Oct 2016 08:37:55 GMT

17 data _null_;
18 infile name;
19 input;
20 put _infile_;
21 if _n_ > 10 then stop;
22 run;

NOTE: The infile NAME is:
Pipe command="zcat "/sas-data/SAD/SAD/IFRS9/BASES/File_name.Z" 2>&1"

gzip: /sas-data/SAD/SAD/IFRS9/BASES/File_name.Z: No such file or directory
NOTE: 1 record was read from the infile NAME.
The minimum record length was 74.
The maximum record length was 74.

I managed to make it work, though. I was missing the input option. Should I post what worked for me as an answer or what?

Re: Reading from Unix

Kurt_Bremser — Fri, 28 Oct 2016 08:58:41 GMT

@sleretrano wrote:
17 data _null_;
18 infile name;
19 input;
20 put _infile_;
21 if _n_ > 10 then stop;
22 run;

NOTE: The infile NAME is:
Pipe command="zcat "/sas-data/SAD/SAD/IFRS9/BASES/File_name.Z" 2>&1"

gzip: /sas-data/SAD/SAD/IFRS9/BASES/File_name.Z: No such file or directory
NOTE: 1 record was read from the infile NAME.
The minimum record length was 74.
The maximum record length was 74.

I managed to make it work, though. I was missing the input option. Should I post what worked for me as an answer or what?

Oh, gosh. Should have noticed that missing input statement.

By all means, post the final solution for future reference.

The 2>&1 redirection of stderr to stdout is one of the most useful tricks when using external commands. I basically never use the x statement any longer in production jobs, I always use filename pipe with redirection, so I get error messages in the log or in files.

Re: Reading from Unix

sleretrano — Fri, 28 Oct 2016 10:45:37 GMT

FILENAME Name pipe "zcat sas-data/SAD/SAD/IFRS 9/BASES/File_name.Z";

Data Test;

infile Name

lrecl=500000

DLM=':'

missover DSD;

Input Var1-Var36;

Run;

The input statement was missing. The above fixes it.

Re: Reading from Unix

sleretrano — Fri, 28 Oct 2016 10:46:36 GMT

No idea of what you're talking about. Can you please refer me to some intro to that?
Thank you for everything.

Re: Reading from Unix

Kurt_Bremser — Fri, 28 Oct 2016 10:51:08 GMT

So the compressed data file really resides in a path relative to the current working directory of the SAS workspace server process.

And it worked just like that, with an unmasked blank in the file name?

I ask that because on my UNIX that results in zcat trying to unzip two separate files. From the commandline and from SAS.

Re: Reading from Unix

sleretrano — Fri, 28 Oct 2016 12:08:33 GMT

Yes, it worked.
I've always heard spaces are a bad idea, but I've never had an issue with them, to be honest.