topic Re: Keep trailing zeros in csv file after using ROUND function in SAS Programming

Keep trailing zeros in csv file after using ROUND function

kmardinian — Thu, 06 Jun 2019 22:35:37 GMT

Hi,

I have a dataset that I would like to output in a csv file after rounding the average of a variable to one decimal place. I also would like whole numbers to appear like this '10.0'. I used a format in my SAS dataset to ensure that whole numbers would keep that trailing zero, but when I export out into a csv file, the trailing zeros are lost.

Is there a way I can ensure that the trailing zeros won't be lost once it is converted into a csv file. I am using proc report to generate my csv file.

Thank you!

data all2;
set all1;
avg=round(average,0.1);
format avg 5.1;
run;

%let rptfile=Average;
%let outpath=\\desktop\;
ODS _ALL_ CLOSE;
ods csv file = "&outpath.&rptfile..csv"
options ( SHEET_NAME="Report"
AUTOFILTER = "all"
EMBEDDED_TITLES ="yes"
ORIENTATION ="landscape"
frozen_headers='3'
absolute_column_width='8, 10, 16, 12'
) ;

title2 "Report Generated";

proc report data=all2 nowd split="~"
style(Header)=[just=left font_weight=bold font_size=10pt]
style(column)={just=left vjust=center font_size=10pt font_face=times background=white foreground=black bordercolor=black};
columns subnum avg ;
define subject/"Subject";
define avg/"Average";

run;
ods excel close;

Keep trailing zeros in csv file after using ROUND Function for sas dataset

kmardinian — Fri, 07 Jun 2019 00:19:47 GMT

Hi, I posted this question in SAS programming, but thought it would fit better here.

Is there a way I can ensure that the trailing zeros won't be lost once it is converted into a csv file. I am using proc report to generate my csv file.

Thank you!

data all2;
set all1;
avg=round(average,0.1);
format avg 5.1;
run;

run;
ods excel close;

Re: Keep trailing zeros in csv file after using ROUND Function for sas dataset

SASKiwi — Fri, 07 Jun 2019 00:26:49 GMT

Examine your CSV file in a text editor, not Excel as it has a default action of not showing decimals if they are zero. If the CSV genuinely has the decimal places using say Notepad, then you can blame Excel.

Re: Keep trailing zeros in csv file after using ROUND function

Tom — Fri, 07 Jun 2019 01:10:33 GMT

Your code looks very mixed up. You have a ODS CLOSE for EXCEL destination, but the OPEN is for CSV destination.

You also have OPTIONS that might be valid for the EXCEL destination, but have no meaning for a CSV file.

Re: Keep trailing zeros in csv file after using ROUND function

PGStats — Fri, 07 Jun 2019 03:28:20 GMT

For a simple task such as writing a CSV file, put proc export to work:

data class;
set sashelp.class;
format weight 7.2;
run;

PROC EXPORT DATA= WORK.CLASS 
            OUTFILE= "&sasforum\reports\csvtest.csv" 
            DBMS=CSV REPLACE;
     PUTNAMES=YES;
RUN;

Name,Sex,Age,Height,Weight
Alfred,M,14,69,112.50
Alice,F,13,56.5,84.00
Barbara,F,13,65.3,98.00
Carol,F,14,62.8,102.50
Henry,M,14,63.5,102.50
James,M,12,57.3,83.00
Jane,F,12,59.8,84.50
Janet,F,15,62.5,112.50
Jeffrey,M,13,62.5,84.00
John,M,12,59,99.50
Joyce,F,11,51.3,50.50
Judy,F,14,64.3,90.00
Louise,F,12,56.3,77.00
Mary,F,15,66.5,112.00
Philip,M,16,72,150.00
Robert,M,12,64.8,128.00
Ronald,M,15,67,133.00
Thomas,M,11,57.5,85.00
William,M,15,66.5,112.00

Re: Keep trailing zeros in csv file after using ROUND Function for sas dataset

andreas_lds — Fri, 07 Jun 2019 05:07:44 GMT

@kmardinian Please don't post one question multiple times. I have merged both posts, to reduce fragmentation.