topic Re: format to one decimal place and still retain the decimal place for integer (whole number) in New SAS User

format to one decimal place and still retain the decimal place for integer (whole number)

femiajumobi1 — Mon, 29 Jan 2024 17:22:33 GMT

Hi. I am creating a dataset and I need a column to retain one decimal place for every value (including integers and thoses I rounded off to once decimal place). In the work library it retains the decimal point but it keeps reformatting integers to whole number when I export in csv. For example, I want 5 to be 5.0 and not 5. I tried these codes below but the csv keeps formatting back to integers: 1, 5, 7 etc.

data new;
set old;
format Measure 13.1;
run;

data new;
set old;
format Measure 13.1;
Measure=round(Measure,0.1)
run;

proc export data=new dbms=csv
outfile="C:\Desktop\new.csv."
replace;
run

Thank you

Re: format to one decimal place and still retain the decimal place for integer (whole number)

SASJedi — Thu, 01 Feb 2024 12:47:40 GMT

PROC EXPORT just exports data - the results are never formatted. You can use PROC PRINT and the ODS CSVALL destination to export the formatted values:

ods csvall file="C:\temp\new.csv";
title;
proc print data=new noobs;
   format Measure 13.1;
run;
ods csvall close;

Re: format to one decimal place and still retain the decimal place for integer (whole number)

ballardw — Mon, 29 Jan 2024 17:46:22 GMT

Did you look in the CSV file with an actual TEXT file veiwer? If you let them default to opening in EXCEL or other spreadsheets they typically ignore the source file values and display them using internal rules which will typically not show trailing 0's.

Also, never save a CSV file that has been opened in spreadsheet software back to the same file as the original values can, like 0 in a decimal place, will be replaced.

Re: format to one decimal place and still retain the decimal place for integer (whole number)

Tom — Mon, 29 Jan 2024 17:47:31 GMT

No. SAS does not do what you describe. Run this test.

data have;
  do x=0,1,1.5,4000;
    output;
  end;
  format x 13.1 ;
run;

filename csv temp;
proc export dbms=csv file=csv data=have;
run;

data _null_;
 infile csv;
 input;
 list;
run;

How did you LOOK at the CSV file?

Did you accidentally let EXCEL open it automatically?

Re: format to one decimal place and still retain the decimal place for integer (whole number)

femiajumobi1 — Mon, 29 Jan 2024 17:52:40 GMT

This did not address it. The value did not retain decimal after integer, 5 remains 5 instead of 5.0

Re: format to one decimal place and still retain the decimal place for integer (whole number)

femiajumobi1 — Mon, 29 Jan 2024 17:55:42 GMT

I opened the csv in the folder it was saved directly. The CSV file opens as csv in a spreadsheet. I am not sure what you meant by not defaulting to open in excel.

Re: format to one decimal place and still retain the decimal place for integer (whole number)

Tom — Mon, 29 Jan 2024 17:56:00 GMT

@femiajumobi1 wrote:
This did not address it. The value did not retain decimal after integer, 5 remains 5 instead of 5.0

Nope.

341  data _null_;
342   infile csv;
343   input;
344   list;
345  run;

NOTE: The infile CSV is:
      (system-specific pathname),
      (system-specific file attributes)

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
1         x 1
2         0.0 3
3         1.0 3
4         1.5 3
5         4000.0 6
NOTE: 5 records were read from the infile (system-specific pathname).
      The minimum record length was 1.
      The maximum record length was 6.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

You have to LOOK at the CSV file using a simple text editor if you want to see what is really in it.

Re: format to one decimal place and still retain the decimal place for integer (whole number)

Tom — Mon, 29 Jan 2024 17:57:47 GMT

If you open the file with a spreadsheet then the spreadsheet program is the one that is deciding how to display the numbers.

A CSV file is just a TEXT file. There is NO WAY to put formatting information (like the 13.1 format that SAS code uses) into a CSV file. It has no place to store such information.

Re: format to one decimal place and still retain the decimal place for integer (whole number)

ballardw — Mon, 29 Jan 2024 18:17:00 GMT

@femiajumobi1 wrote:
I opened the csv in the folder it was saved directly. The CSV file opens as csv in a spreadsheet. I am not sure what you meant by not defaulting to open in excel.

Spreadsheet software is determining the appearance.

Open with a TEXT viewer, Notepad, Wordpad, any text editor. But only if you did not SAVE after the spreadsheet opened the file.

Your operating system has defaults as to which program opens which files for many different file types. Unfortunately for those of us that work with CSV files that means your spreadsheet defaults as the program to open them in many cases. You can typically override that behavior by using the Open With option (depending on operating system right click on the file name in your file manager and see what shows up). You may have to select a different application of program.

Re: format to one decimal place and still retain the decimal place for integer (whole number)

femiajumobi1 — Mon, 29 Jan 2024 18:23:51 GMT

Thanks @ballardw

Back to my question, how do I ensure in csv the value comes with one decimal point (5.0) and not default back to an integer (5). Thanks.

Re: format to one decimal place and still retain the decimal place for integer (whole number)

femiajumobi1 — Mon, 29 Jan 2024 19:06:32 GMT

I saw these in the log.

Again I am interested in formatting every number to one decimal place including integers. For example I want SAS to retain 10 as 10.1 when I export the file in csv and 10.

Re: format to one decimal place and still retain the decimal place for integer (whole number)

Tom — Mon, 29 Jan 2024 19:43:47 GMT

Didn't you see decimal place in the text file when you used the data step to dump it to the log?

If you want the report to display precisely produce a PDF file. That is what they are for.

If you want to force a spreadsheet to open a file with particular format then you will need to generate and XLSX file and add code to format the cells with the those numbers to display using one decimal place.

https://communities.sas.com/t5/ODS-and-Base-Reporting/From-SAS-to-Excel-keeping-number-type-while-formatting/td-p/845694

Re: format to one decimal place and still retain the decimal place for integer (whole number)

Tom — Mon, 29 Jan 2024 19:46:13 GMT

@femiajumobi1 wrote:

I saw these in the log.

Again I am interested in formatting every number to one decimal place including integers. For example I want SAS to retain 10 as 10.1 when I export the file in csv and 10.

No. You are formatting the strings written into the CSV file.

You appear to want a method to tell a Spreadsheet program to display the numbers using a single decimal place. That is not something you can do with a CSV file.

Re: format to one decimal place and still retain the decimal place for integer (whole number)

andreas_lds — Wed, 31 Jan 2024 07:36:02 GMT

@femiajumobi1 wrote:
Thanks @ballardw

Back to my question, how do I ensure in csv the value comes with one decimal point (5.0) and not default back to an integer (5). Thanks.

As long as you insist on opening a csv file with a spreadsheet app and not with a simple text editor, there is no solution for the problem. A csv file is a text file, it does not contain any metadata, so it is impossible to tell the app to display values as they are and skip optimizing the data.

Re: format to one decimal place and still retain the decimal place for integer (whole number)

SASJedi — Thu, 01 Feb 2024 12:55:43 GMT

Here is a screenshot of the CSV file that was generated. You can clearly see that the results are exactly as you specified:

If you open the file in Windows using Notepad, Notepad++, or any text editor, you will see the same. If you open the file with Excel, Excel will automatically change the way the data is displayed and you will no longer see the trailing zeros:

You can't make Excel change this behavior. If you want this to look different in Excel, then you will have to create an Excel file instead of a CSV.

ods excel file="C:\temp\new.xlsx";
title;
proc print data=new noobs;
   format Measure 13.1;
run;
ods excel close;

This writes an XLSX file, which includes the metadata Excel requires to display the data the way you want: