BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Manu_SAS
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Manu_SAS
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Manu_SAS
Obsidian | Level 7

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I only have PC SAS, sorry.

Tom
Super User Tom
Super User

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;

Cynthia_sas
SAS Super FREQ

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

Manu_SAS
Obsidian | Level 7

Hi Cynthia,

Understood the problem. That was really a great help.

You are correct.

Regards

Manohar

nash_sas
Fluorite | Level 6

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 ?

Manu_SAS
Obsidian | Level 7

Hi Tom,

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

Regards

Manohar

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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