BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
femiajumobi1
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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?

View solution in original post

14 REPLIES 14
SASJedi
SAS Super FREQ

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;

 

Check out my Jedi SAS Tricks for SAS Users
femiajumobi1
Quartz | Level 8
This did not address it. The value did not retain decimal after integer, 5 remains 5 instead of 5.0
Tom
Super User Tom
Super User

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

femiajumobi1
Quartz | Level 8

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. 

Tom
Super User Tom
Super User

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

Tom
Super User Tom
Super User

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

SASJedi
SAS Super FREQ

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

SASJedi_0-1706791342314.png

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:

SASJedi_2-1706791547513.png

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:

 

SASJedi_3-1706792020454.png

 

 

 

 

Check out my Jedi SAS Tricks for SAS Users
ballardw
Super User

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.

femiajumobi1
Quartz | Level 8
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.
Tom
Super User Tom
Super User

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.

ballardw
Super User

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

femiajumobi1
Quartz | Level 8
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.
andreas_lds
Jade | Level 19

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

Tom
Super User Tom
Super User

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?

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 14 replies
  • 4379 views
  • 4 likes
  • 5 in conversation