BookmarkSubscribeRSS Feed
Haris
Lapis Lazuli | Level 10

I want to output a percent-formatted variable to Excel.  Very excited about the capabilities of the new ODS EXCEL but it misbehaves on me.

I used three methods to print a percetn8.2 formatted variable: PRINT, SQL, REPORT.  All three produce different results.

PRINT is the worst--it truncates the number to two decimals, outputs some of the numbers as text with '%' and others as numbers without '%' without truncation but rounded to two decimals.

SQL outputs the variable properly, truncates all numbers to two digits but output is in text column rather than number.

REPORT is the best -- outputs number with '%', does not truncate the number, but I can't get it to show how any decimals it rounds to a full number.

Here's the code and I am running SAS 9,4 M2:

data Class; set SASHelp.Class;

  PctWt = Height/Weight;

  format PctWt percent8.2;

run;

ods excel file="d:\Temp\Test.xlsx" style=Printer;

ods excel options(sheet_name='Print');

proc print data=Class;

run;

ods excel options(sheet_name='SQL');

proc sql;

  select *

  from Class;

quit;

ods excel options(sheet_name='Report');

proc report data=Class;

run;

ods excel close;

22 REPLIES 22
Ksharp
Super User

Transform it into CHARACTER type

Haris
Lapis Lazuli | Level 10

Really?  To move percent-formatted columns from SAS to Excel you recommend that I turn them to text?  I hope that's not the answer!  PROC SQL already does that in the example above...

Ksharp
Super User

An alternative way is defining a special style :  style={ tagattr='format:@'  }

SROMAA
Calcite | Level 5

I use PROC REPORT and specify the format on the define line.  ExcelXP was touchy and didn't like data to be pre-formatted so you may need to remove the format statement in the data step to make ODS Excel apply the intended format.  In your case it would look like this:

proc report data=class;

     define pctwt / display f=percent8.2;

run;

Haris
Lapis Lazuli | Level 10

Like the idea of not using the format in the dataset and formatting in at the output stage.  Unfortunately, makes no difference in output--Excel still has shows percent8.0 rather than 8.2 that was requested by PROC REPORT.

I hope this is a bug that SAS will work out by the time ODS EXCEL graduates from testing stage.   

SROMAA
Calcite | Level 5

Did you remove the format from the data step?  I tested with the format in PROC REPORT only using both PERCENT8.2 and PERCENT8.3 and the XLSX file created by ODS EXCEL contained the correct format both times.  Another issue may be the version of Excel used to open the XLSX file.  I am using Excel 2013.

SROMAA
Calcite | Level 5

Specifically, the following code works fine:

ods excel file="%sysfunc(pathname(WORK))\test.xlsx"

options(sheet_name='Report');

proc report data=SASHEP.CLASS;

columns height weight pctwt;

define height / display order=internal;

define weight / display;

define pctwt / computed f=percent8.1;

compute pctwt;

pctwt=height/weight;

endcomp;

run;

ods excel close;

Haris
Lapis Lazuli | Level 10

SROMAA, I have Excel 2010.  What version of SAS are you using?  I still get percentages rounded to a full number or percent8.0 format, not percent8.1.

SROMAA
Calcite | Level 5

I'm using SAS 9.4M1.  This is nuts!  The only other thing I know to try is using the tagattr suggested by SASKiwi.

SASKiwi
PROC Star

BTW, my version works OK in the Excelxp tagset, so if you are unable to get it working using ODS EXCEL you could try EXCELXP.

SROMA's program works fine for me using SAS 9.4 M2 and Excel 2010. When I highlight the percent column in Excel, it tells me a custom format has been applied, which looks like this: ########"%"_);(########"%")

Haris
Lapis Lazuli | Level 10

SASKiwi, I get the same but this is not what the program asks.  We should be getting #######.#"%"_);(#######.#"%") not ########"%"_);(########"%").  Now I am stuck with correcting the format manually and I have hundreds of columns.

The current program is using ExcelXP tagset but we need to include graphs in the output which ExcelXP does not do.  Hence the need to migrate from ODS Tagsets.ExcelXP to ODS Excel.

SASKiwi
PROC Star

Try this as a DEFINE column in PROC REPORT:

define MyPercent pctsum "My Percent" format = percent10.1 style=[tagattr="format:#,##0.0%"];

Vamongeau
Calcite | Level 5

I don't use proc report, but with proc tabulate, I use the following formats.

picture pctfmt(round) low-high= "009.9%";

picture multi(round) low-high= "009.9%" (multiplier=1000) ;

I then apply them to the variable when output via tabulate.

Don't know if this will help you with proc excel.

Haris
Lapis Lazuli | Level 10

Is anyone from SAS tech support monitoring these threads?  I should probably open a ticket with this issue or at least let them know is some way...

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 22 replies
  • 10761 views
  • 2 likes
  • 9 in conversation