turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- ODS and Base Reporting
- /
- Exporting formulas from SAS to Excel

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-24-2015 05:38 PM

I wanted to export formulas from SAS to Excel, using ODS EXCEL. However, the Excel file did not recognize the formulas. It showed the formulas instead of the calculated numbers. Does anyone know how to fix the problem? Thank you.

Below are the codes I used. (You can run them if you have SAS 9.4.)

-------------------------------------------

proc sql;

create table cars as

select distinct make, model, msrp, invoice

from sashelp.cars

where make = 'Audi'

; quit;

data cars_b; set cars;

row_num + 1;

if first.make then row_num = 1;

row_num_b = strip(put(sum(row_num,4),8.));

diff = '='||'C'||row_num_b||'-'||'D'||row_num_b;

diff_b = compress(diff);

run;

options missing = ' '

papersize = letter

topmargin = 0.5 in

leftmargin = 0.5 in

rightmargin = 0.5 in

bottommargin = 1.0 in

;

ods excel

file = 'I:\1 Academic Affairs\Faculty\1 Salary\FERIP\ODS EXCEL (test)\diff_in_prices.xlsx'

style = normal

options (

embedded_titles = 'yes'

embedded_footnotes = 'yes'

print_footer = ' '

center_horizontal = 'yes'

absolute_column_width = '8,30,12,12,12'

fittopage = 'no'

pages_fitheight = '100'

orientation = 'portrait'

row_repeat = '3-5'

sheet_interval = 'none'

sheet_name = 'Diff'

merge_titles_footnotes = 'yes'

title_footnote_width = ' '

);

title1 'XXX';

title2 'Difference in Price';

proc print data = cars_b label NOOBS;

var make model msrp invoice diff_b;

label diff_b = 'Difference';

run;

footnote;

ods excel close;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-24-2015 05:46 PM

Have you tried adding in formulas='yes' to the options?

Its in Tagsets.excelxp but not sure about ODS Excel

EDIT: According to page 18 it's still valid - http://support.sas.com/resources/papers/proceedings14/SAS177-2014.pdf

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-24-2015 05:59 PM

I just added formulas = 'yes' to the options, but it still did not work.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-24-2015 06:11 PM

I think you need to use RC format not A# format.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-24-2015 06:28 PM

Thank you Reeza. I used ods excelxp tagset (instead of ods excel) and RC format. It worked now. This is my next problem: I wanted to format the calculated column, using the dollar format. However, SAS did not accept it as I was trying to use a numeric format to a character column. Do you have any solutions for this? Many thanks!

proc print data = cars_b label NOOBS;

format diff_b dollar9.;

var make model msrp invoice diff_b;

label diff_b = 'Difference';

run;

ERROR: You are trying to use the numeric format DOLLAR with the character variable diff_b in data set WORK.CARS_B.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-24-2015 07:04 PM

You need to add an Excel-style format not a SAS one as it is applied once the formula is calculated in Excel:

var diff_b style=[textalign=right tagattr="format:$#,##0.00"];

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-26-2015 09:43 AM

Thank you!