The SAS Output Delivery System and reporting techniques

Prevent the loss of leading zeros with the ODS CSV destination(SAS 9.4 GRID)

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Prevent the loss of leading zeros with the ODS CSV destination(SAS 9.4 GRID)

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


Accepted Solutions
Solution
‎02-12-2015 01:42 PM
SAS Super FREQ
Posts: 8,868

Re: Prevent the loss of leading zeros with the ODS CSV destination(SAS 9.4 GRID)

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

View solution in original post


All Replies
Super User
Super User
Posts: 7,997

Re: Prevent the loss of leading zeros with the ODS CSV destination(SAS 9.4 GRID)

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.

Contributor
Posts: 20

Re: Prevent the loss of leading zeros with the ODS CSV destination(SAS 9.4 GRID)

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

Super User
Super User
Posts: 7,997

Re: Prevent the loss of leading zeros with the ODS CSV destination(SAS 9.4 GRID)

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

Contributor
Posts: 20

Re: Prevent the loss of leading zeros with the ODS CSV destination(SAS 9.4 GRID)

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

Super User
Super User
Posts: 7,997

Re: Prevent the loss of leading zeros with the ODS CSV destination(SAS 9.4 GRID)

I only have PC SAS, sorry.

Super User
Super User
Posts: 7,077

Re: Prevent the loss of leading zeros with the ODS CSV destination(SAS 9.4 GRID)

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;

Solution
‎02-12-2015 01:42 PM
SAS Super FREQ
Posts: 8,868

Re: Prevent the loss of leading zeros with the ODS CSV destination(SAS 9.4 GRID)

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

Contributor
Posts: 20

Re: Prevent the loss of leading zeros with the ODS CSV destination(SAS 9.4 GRID)

Posted in reply to Cynthia_sas

Hi Cynthia,

Understood the problem. That was really a great help.

You are correct.

Regards

Manohar

Occasional Contributor
Posts: 16

Re: Prevent the loss of leading zeros with the ODS CSV destination(SAS 9.4 GRID)

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 ?

Contributor
Posts: 20

Re: Prevent the loss of leading zeros with the ODS CSV destination(SAS 9.4 GRID)

Hi Tom,

Thanks for your reply. That was really helpful as you said excel will convert the formatting ending in mess up.

Regards

Manohar

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1592 views
  • 5 likes
  • 5 in conversation