Formatting columns in exporting SAS table to Excel spreadsheet

Question

I am automating the exporting of a data set from SAS to Excel using ODS ExcelXP:

       PURCHASE_    annual_      Minimum_
Obs       APR         fee      Cash_Advance
  1     23.45%       NONE          $10
  2     23.45%       NONE          $10
  3     23.45%       NONE          $10
  4     18.45%       NONE          $10
  5     18.45%       NONE          $10
  6     18.45%       NONE          $10
  7     23.45%       NONE          $10
  8     23.45%       NONE          $10
  9     23.45%       NONE          $10
 10     23.45%       $0
 11     23.45%       $0
 12     23.45%       $0

In SAS, the columns are formatted as text and I want all of the columns to be imported as text into Excel. I've used the following code to create the file using PROC REPORT:

ods tagsets.ExcelXP path="H:/path"  file="file.xls" style=myStyle
                    options(frozen_headers='yes' WrapText='no'
                            embedded_titles='yes' suppress_bylines='yes'
                            sheet_interval='none' sheet_label=' ' 
                            sheet_name='Solicited'
                            width_points='1' width_fudge='1' 
                            absolute_column_width='100' autofit_height='yes' 
                            zoom='100');
title1;

proc report data=testing2 nowd;
  column purchase_APR annual_fee minimum_cash_advance;
  define purchase_APR / display style(column)={tagattr='format:@'} 'PURCHASE_APR';
  define annual_fee / display style(column)={tagattr='format:@'} 'ANNUAL_FEE';
  define minimum_cash_advance / display style(column)={tagattr='format:@'} 'MINIMUM_CASH_ADVANCE';
run;

ods tagsets.ExcelXP close;

However, when opening up the Excel file, the Text fields have been somehow changed from 23.45%, $0, and $10 (text) to 0.2345, 0, and 10 (text) respectively.

How can I get the output in Excel to be just like the data set in SAS?

I have tried using the specific formats to get them to look the same (i.e.tagattr='format:0.00%', etc.) but the output in Excel is numeric and not text format.

Joe · Accepted Answer

The proper way would be to modify how the template processes numbers. You can do that pretty easily in this case. You could even just comment out a line and one block of code, but here's the really proper answer.

Open the template in a text editor. We're going to add a couple of parameters, and implement them.

First, add the options to the $valid_options array. There are a bunch of lines like these, add these two more (Around line 635 or so):

set $valid_options["TEXTPERCENT"] "This value forces percentages to be displayed as text";
set $valid_options["TEXTCURRENCY"] "This value forces currency amounts to be displayed as text";

That text can be whatever you want, this is one interpretation. Now, around line 700 there are some lines setting the defaults, add these two:

set $option_defaults["TEXTCURRENCY"] 'no';
set $option_defaults["TEXTPERCENT"] 'no';

Now down much later (around row 1670) you have the section that defines $punctuation. We change how that works in order to remove "%" and "$" from the list if you set those options:

set $punctuation $thousands_separator " ";
set $punctuation $punctuation "%" /if ^$textpct;
set $punctuation $punctuation $currency_sym /if ^$textcurr;

(Basically, set $variable /if ; we set up punctuation to start with $thousands_separator and then add in the other bits if they are "no" only.)

Now around line 2100 in the "Yes/no on/off options... " section we evaluate the option's value. (The prior uses these values, but that's okay; it's actually called later.)

set $option_key 'TEXTPERCENT';
trigger do_yes_no;
eval $textpct $answer;

set $option_key 'TEXTCURRENCY';
trigger do_yes_no;
eval $textcurr $answer;

Finally, we implement things. Down around line 7400 is event value_type; which is where the % $ get removed and the numbers get adjusted to be 'real numbers' even if they shouldn't be. This is annoying. So we tell it not to.

do /if ^$textpct;
 do /if $convert_percentages;
   eval $tmp inputn($value, $test_format)/100;
 else;
   eval $tmp inputn($value, $test_format);
 done;
 /*putlog "Percent value:" $tmp;*/
 set $value $tmp;
done;

We wrap the percent conversion code with do /if ^$textpct; and done, which tells it to skip doing the inputn (which will kill our percents). If we were cheating and not doing this the proper way, we could comment out this line:

set $value compress($value, $punctuation);

But since we fixed the $punctuation variable to contain (or not contain!) the right stuff already, this isn't an issue.

Now this will work! We just modify the tagset call:

*First include your tagset, which I put in c:	emp\ but you can put wherever and call whatever you like;
%include "c:	emp\excel_tpl_nocompress.txt";
 ods tagsets.ExcelXP path="c:	emp"  file="testfile.xml" 
                    options(frozen_headers='yes' WrapText='no'
                            embedded_titles='yes' suppress_bylines='yes'
                            sheet_interval='none' sheet_label=' ' 
                            sheet_name='Solicited' convert_percentages="no"
                            width_points='1' width_fudge='1' 
                            absolute_column_width='100' autofit_height='yes' 
                            textcurrency='yes' textpercent='yes'
                            zoom='100');
title1;
*Then add in the textpercent and textcurrency lines, and it should work as is.;

And now you're off to the races.

- 
-    23.45%    
-        
-    $0

Formatting columns in exporting SAS table to Excel spreadsheet

Answers (2)

Related Questions