topic Re: Problem to export a SAS dataset into a readable CSV file on Unix. in SAS Programming

Problem to export a SAS dataset into a readable CSV file on Unix.

Jbaax — Wed, 19 Oct 2016 13:44:53 GMT

Hello everybody,

I've a problem with exporting a SAS dataset into a readable CSV file on a Unix Server.

First I've tried a proc export but when I load the result on the unix server, all the observations are unreadables.
Second, I've red the post : https://communities.sas.com/t5/SAS-Procedures/PROC-Export-not-working-on-UNIX-because-of-terminal-option/m-p/78958#M22765 and trie the solution of Tom with a data _null_ but the CSV is already unreadable.

Have you an idea to solve this problem ? Please help me to resolves this dos2unix problem !

PS : However, if i'm opening my csv file on Excel, and i save it on CSV with Excel, the file is readable on Unix.

Regards

Re: Problem to export a SAS dataset into a readable CSV file on Unix.

RW9 — Wed, 19 Oct 2016 13:49:41 GMT

Sorry, could we step back a bit here. How are you creating the CSV, is this via SAS? Is SAS on Unix or Windows? What do you mean by "load the result"? What is meant by "all the observations are unreadables" - unreadable by what?

First guess I would say is that carriage return/line feeds are different between Windows and Unix, from memory this needs to be changed if the file is created on one and opened in the other, for instance: http://support.sas.com/kb/14/178.html

Also note that CSV is not an Excel file, the two are different so make sure your "CSV" is plain text delimited file. And if you need to transmit the file between OS's use FTP.

Re: Problem to export a SAS dataset into a readable CSV file on Unix.

Jbaax — Wed, 19 Oct 2016 14:07:47 GMT

Thank you for getting back to me so quickly. I'm going to try and clarify my issue.

Yes, I'm creating the CSV via SAS, using a proc export :

proc export data = test

outfile=" test.csv"

dbms=csv

replace;

delimiter=" ;" ;

run;

SAS is running on a Unix server, but I'm executing my code from an Enterprise Guide client on a Windows machine. I hope that makes sense.

So I'm trying to create a CSV file that I need to use in a subsequent sas program. This second program reads the csv with 0 observations, although if I open the file on my Windows, I can see all of the rows correctly.

I should also mention that I'm using Filezilla (so an FTP) to transfer the csv between Windows and Unix.

Even when I open the file from Unix with a simple text editor, I can still see my rows.

The problem only arises when I try and open the file stored on Unix with a SAS program.

I'm assuming that the problem comes from the file format difference between Unix and Windows, but do you have a different suggestion?

I get this in my log:

NOTE: 0 records were read from the infile "/mypath/test.csv"

Re: Problem to export a SAS dataset into a readable CSV file on Unix.

RW9 — Wed, 19 Oct 2016 14:16:32 GMT

At a guess - and I am just on my way out here so maybe someone else can pick up for a bit, it could be because of the delimiter. CSV stands for Comma Delimeted Variable file. Looking at the code you have posted, you are putting a semicolon between data items, hence the file is not a CSV file. When you read in that Delimited text file, make sure you specify semicolon as a delimiter.

However that being said you should stilll get some records.

What does:

data _null_;
  infile "/mypath/test.csv";
  input;
  put _infile_;
run;

Show?

Re: Problem to export a SAS dataset into a readable CSV file on Unix.

ballardw — Wed, 19 Oct 2016 14:18:11 GMT

And exactly how are you opening the file stored on Unix with a SAS program?

Reading with a data step, proc import or something else.

BTW you say you create a CSV but your export says to use the semicolon as a delimiter. So if you try to read something as CSV with semicolons it won't work as the wrong delimiter is encountered.

Re: Problem to export a SAS dataset into a readable CSV file on Unix.

Tom — Wed, 19 Oct 2016 14:21:38 GMT

If you are creating the file on Unix and reading it on Unix then you should not have any troubles.

If both the reading and writing steps are running on the same machine then make sure to use the same path to the file in both programs.

Note that the sample code you posted created a file with leading space in the filename. Perhaps you are creating one file and reading an older version of the file with a different name.

If you are moving the file between systems then check if FTP is treating the file as binary or text. If one method doesn't work then try other.

Use a simple program to look at the file and make sure it has the data you expect. Run it both after creating it and before trying to read it to confirm the file is the same.

data _null_;
  infile "test.csv" obs=5 ;
  input;
  list;
run;

Re: Problem to export a SAS dataset into a readable CSV file on Unix.

Jbaax — Wed, 19 Oct 2016 14:24:48 GMT

The ; is because my systems are French and in France csv is delimited by a semicolon (because numbers have , as a decimal point...). In fact I've found a solution that works, it's not when writing the csv file, but rather at the reading stage. The option that works for me is : infile "myfile.csv" encoding="utf-8" TERMSTR=LF dlm=";" missover dsd;

Re: Problem to export a SAS dataset into a readable CSV file on Unix.

RW9 — Wed, 19 Oct 2016 14:29:52 GMT

Hi,

Well, good that solved it. Just to note however that CSV is a (pretty much) standard file format - the title is Comma Separated Variable. What you have is a delimited file - thus it would be a good idea to name it appropriately (.txt) otherwise someone else will assume the file is comma delimited.

https://en.wikipedia.org/wiki/Comma-separated_values