Hello Everyone,
I'm trying to generate a .csv file in SAS 9.2, for that I used the options Prepend_equals and quote_by_type to generate values "0.00" Instead of "0".
ods csvall file="path/aaa.csv" options(Prepend_Equals='Yes' quote_by_type="Yes" );
Now I'm trying to generate same .csv in SAS 9.4 GRID Server, to generate the values "0.00". But I'm getting "0" values.
Anyone help me out in resolving the problem?
Thanks,
Manohar
There is a huge difference between how the SAS output looks in a SAS viewer (like the Results Window) and how the CSV output look once Excel opens the CSV file. Excel has default column widths and default column formats that it uses with a CSV file and it will basically ignore your SAS format. Sometimes the suboptions like quote_by_type will be OK when Excel gets the file and sometimes you get the dreaded "green triangle" in Excel for "number stored as text" error.
If the OP would use ODS and style overrides, he/she could force Excel to render the data values as desired, but the report type would no longer be CSV. It would be either HTML-based output (ODS MSOFFICE2K) or XML based output (ODS TAGSETS.EXCELXP).
See this paper for some examples: http://support.sas.com/resources/papers/proceedings11/266-2011.pdf One of the first examples in the paper is a leading zero example.
cynthia
Are you setting the value as a text field, i.e. put(value,8.2)? With text you can put anything you like in.
TBH though my opinion is that if you are sending CSV data (or any other structured transfer format) it should be the value as is. So 0 is 0, 0.00 doesn't really exist other than as a concept. So it should be the responsibility of the reading program to read that data in as 0.00 format. This would of course be described in the data transfer agreement that the data is of type float 8.2.
Hi RW9,
Thank you for your replay.
My data-set comes with the value 0.00 format 8.2, the value remains the same as "0". From SAS dataset end, Format is working fine. When it comes to CSV file in SAS 9.4, format is missing. Options are not working in 9.4 SAS ODS CSV!
Thanks,
Manohar
Well, I ran this program:
ods csvall file="s:\temp\rob\aaa2.csv" options(Prepend_Equals='Yes' quote_by_type="Yes" );
data tmp;
format a 8.2;
a=0;
run;
proc print data=tmp;
run;
ods csvall close;
In both 9.3 and 9.4 and it produce the same results (must admit I was surprised it output .00), I don't have 9.2 so can't check that, however this matches the output you have stated as coming from 9.2. So makes me wonder about your specific install. Maybe worth checking with a helpdesk ticket
The SAS System
"Obs","a"
"1",0.00
Hi Rob,
Yeah, It's working fine in PC SAS 9.4, but actually we are running the code in Unix Grid Server end.
It would be great, if you try in grid server.
Thanks,
Manohar
I only have PC SAS, sorry.
Are you sure? How did you look at the generated file? If you opened it into EXCEL then EXCEL will convert the formatting.
Try this simple program.
filename xx temp;
ods _all_ close;
ods csvall file="%sysfunc(pathname(xx))" options(Prepend_Equals='Yes' quote_by_type="Yes" );
data tmp;
format a 8.2;
a=0;
run;
proc print data=tmp;
run;
ods csvall close;
ods listing;
data _null_;
infile xx ;
input;
put _all_;
run;
There is a huge difference between how the SAS output looks in a SAS viewer (like the Results Window) and how the CSV output look once Excel opens the CSV file. Excel has default column widths and default column formats that it uses with a CSV file and it will basically ignore your SAS format. Sometimes the suboptions like quote_by_type will be OK when Excel gets the file and sometimes you get the dreaded "green triangle" in Excel for "number stored as text" error.
If the OP would use ODS and style overrides, he/she could force Excel to render the data values as desired, but the report type would no longer be CSV. It would be either HTML-based output (ODS MSOFFICE2K) or XML based output (ODS TAGSETS.EXCELXP).
See this paper for some examples: http://support.sas.com/resources/papers/proceedings11/266-2011.pdf One of the first examples in the paper is a leading zero example.
cynthia
Hi Cynthia,
Understood the problem. That was really a great help.
You are correct.
Regards
Manohar
Just have double/single quotes qualfiers for all the variables and do a simple proc export. Why the equal sign ("= 0...") instead of just double/single quotes, if it's OK to have output .csv files be opened with text editors like notepad or wordpad ?
Hi Tom,
Thanks for your reply. That was really helpful as you said excel will convert the formatting ending in mess up.
Regards
Manohar
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.