topic Re: ODS csv in ODS and Base Reporting

ODS csv

Sharukh — Wed, 17 Aug 2016 09:24:53 GMT

I am running below program it is crating csv file but it is empty, i able to see output in EG(output dataset).

ods CSVALL file="/home/xxx/myoutput1.csv";

data sapmle1;
set sample;
/*date= MONTH(birth);*/
today=date();
/*monthoftoday=MONTH(today);*/
totledays=(today-birth);
if totledays<=180;
format today ddmmyy.;

run;
ods CSVALL close;

Re: ODS csv

RW9 — Wed, 17 Aug 2016 10:12:11 GMT

You have no output creating procedure. Datasteps create datasets, they don't create output. You shoul use proc print or report or something simliar to create output:

ods csvall file="c:\myoutput1.csv";
proc print data=sashelp.class;
run;
ods csvall close;

Re: ODS csv

xd9813 — Mon, 27 Apr 2020 22:22:31 GMT

Is there a way to produce the same output as the %ds2csv macro using ODS? I am looking for double quotes around values and only the %ds2csv SAS macro seems to do it. Thanks

Re: ODS csv

Tom — Mon, 27 Apr 2020 22:49:49 GMT

@xd9813 wrote:

Is there a way to produce the same output as the %ds2csv macro using ODS? I am looking for double quotes around values and only the %ds2csv SAS macro seems to do it. Thanks

Why do you want extra bytes in your CSV file?

Is there some specific program/system that you are transferring the data to that required the unneeded quotes?

Can you please name it? Public shaming is a good way to put pressure on them to make their systems more robust.

If you just want to write a CSV file then there is no need for a PROC or ODS. A simple data step will do it. And you can have full control over how the file is written.

Re: ODS csv

xd9813 — Mon, 27 Apr 2020 22:57:25 GMT

Some users in the org open these CSV files in MS Excel. As a result, the fields that have leading zeros or card numbers get truncated. Additionally, importing the same dataset to Excel has caused file corruption with that Excel needs to repair message while opening the workbook.

I am looking for a way to replicate db2csv which is a SAS system macro.

Thank you.

Re: ODS csv

Tom — Mon, 27 Apr 2020 23:08:32 GMT

Quotes around the values will not slow down Excels ability to mess up the import AT ALL.

Re: ODS csv

xd9813 — Mon, 27 Apr 2020 23:10:36 GMT

Quotes work, I tested. Excel does not truncate data if surrounded by quotes. Thanks

Re: ODS csv

Tom — Mon, 27 Apr 2020 23:11:17 GMT

If you need to use the data in Excel then generate XLSX files. You can use ODS EXCEL or the XLSX libname engine.

Re: ODS csv

Tom — Mon, 27 Apr 2020 23:30:09 GMT

@xd9813 wrote:
Quotes work, I tested. Excel does not truncate data if surrounded by quotes. Thanks

If you are careful HOW you open the file then you can prevent Excel from interpreting strings as numbers or dates, whether the quotes are there are not has never made any difference in my experience. I have never heard of Excel truncating any values, but it did use to have a problem with writing long strings in a single cell, but that was decades ago. Does you data have long strings?

Here is simple SAS program to dump a dataset to a CSV file with every value in quotes, like the %ds2csv() macro does.

First let's set up an example dataset and target filename.

data test1;
  set sashelp.class(obs=3);
run;
filename csv3 temp;

Now here is the code. One PROC and two data steps. The PROC is to get the names. If you don't need the header row then just use the last data step without the MOD option on the FILE statement (unless you want to append to an existing file). The tilde modifier on the PUT statement is what is adding the quotes. If you remove that then the DSD option will only add the quotes where they are NEEDED.

proc transpose data=test1(obs=0) out=names ;
 var _all_;
run;
data _null_;
  file csv3 dsd ;
  set names end=eof;
  put _name_ ~ @ ;
  if eof then put;
run;
data _null_;
  file csv3 dsd mod;
  set test1;
  put (_all_) (~);
run;

Let's take a look at the results:

157  data _null_;
158   infile csv3;
159   input;
160   put _infile_;
161  run;

NOTE: The infile CSV3 is:
      Filename=.../#LN00050,
      Owner Name=...,Group Name=...,
      Access Permission=-rw-rw-r--,
      Last Modified=27Apr2020:19:06:53,
      File Size (bytes)=128

"Name","Sex","Age","Height","Weight"
"Alfred","M","14","69","112.5"
"Alice","F","13","56.5","84"
"Barbara","F","13","65.3","98"
NOTE: 4 records were read from the infile CSV3.
      The minimum record length was 28.
      The maximum record length was 36.

Re: ODS csv

xd9813 — Mon, 27 Apr 2020 23:22:32 GMT

I tried ODS Excel with PROC Print. End user gets this error.

tried PROC Export next... same issue.

Re: ODS csv

Tom — Mon, 27 Apr 2020 23:24:16 GMT

What version of SAS are you using?

162  %put &=sysvlong;
SYSVLONG=9.04.01M5P091317

Re: ODS csv

Tom — Tue, 28 Apr 2020 00:25:09 GMT

Usually I see that error when I run code like in your original post. You opened the ODS destination, but never wrote anything to it, and then closed it. SAS does not make a working XLSX file in that case. Not sure what a working XLSX file with no sheets in it would look like.

Do you have an encoding issue? What encoding is your SAS session using? Does any of your character data using non-7 bit ASCII codes?

How are your Excel users getting access to the XLSX file? Do they have to move them via a file transfer tool like FTP? If so make sure to move them as binary and not as text.