BookmarkSubscribeRSS Feed
Sharukh
Calcite | Level 5

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;

11 REPLIES 11
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
xd9813
Fluorite | Level 6

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

Tom
Super User Tom
Super User

@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.

xd9813
Fluorite | Level 6

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. 

Tom
Super User Tom
Super User

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

xd9813
Fluorite | Level 6
Quotes work, I tested. Excel does not truncate data if surrounded by quotes. Thanks


Tom
Super User Tom
Super User

@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.

 

Tom
Super User Tom
Super User

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

xd9813
Fluorite | Level 6

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

xd9813_0-1588029664705.png

tried PROC Export next... same issue. 

 

Tom
Super User Tom
Super User

What version of SAS are you using?

162  %put &=sysvlong;
SYSVLONG=9.04.01M5P091317
Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 5331 views
  • 2 likes
  • 4 in conversation