Hi all,
I'm hoping someone can assist me with this - I have written some code that creates a data set where a couple of the variables are Excel formulas. What I want to be able to do is then to export the data set as an Excel file and for the formulas to be active as soon as I open the sheet.
This is the part of the code that creates the Excel formula variables (the variables in question are Exchange1 and backed): -
data all_sel_4_1;
set datasets.all_sel_2_1_&csv_file.;
length Exchange1 $ 300;
horse = compress(horse,"`");
cell_1 = strip(put(_n_ + 1,best.));
cell_2 = strip(put(_n_ + 4,best.));
Exchange1 = cats("=IF(ISNA(VLOOKUP(D",cell_1,",&excel.,3,FALSE)),",'"','"',",VLOOKUP(D",cell_1,",&excel.,3,FALSE))");
backed = cats('=IF(F',cell_1,'="",0,1)');
time_run = timestamp;
drop cell_1 cell_2 timestamp;
run;
I have little experience in using ods but if I try to create an xlsx file using this code
ods excel file="/folders/myfolders/Excel/Outputs/&csv_file._Merge_2.xlsx" style=normal options(formulas='on');
proc print data=all_sel_4_1 noobs;
run;
ods excel close;
the xlsx file is created but when I open it, the Exchange1 variable shows the full text of the formula in the cell. If I click the cell, press F2 and enter, that activates the formula (forgive me if that isn't the correct terminology). The formula for the backed variable is active as soon as I open the sheet.
To get around this, I have been using ods html and creating an xls file: -
ods html file="/folders/myfolders/Excel/Outputs/&csv_file._Merge_2.xls" style=normal options(formulas='on');
proc print data=all_sel_4_1 noobs;
run;
ods html close;
This does work but when I open the file, it gives me a warning about the file format and extension not matching. I would like to be able to use ods Excel but am just not sure what I need to do differently in order to ensure that all the formulas are recognised as such when the file is opened. The only difference I can see in how the two formulas are created is around the quote marks I have used, but in the Exchange1 variable, I need to use the double quotes in order to resolve the macro variable.
As always, any ideas or suggestions will be gratefully received.
Thanks,
Rob
Please have a look at the following. They may help.
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/hostwin/p0iru729p1j2f1n1cw3sjgg6emcv.htm
https://www.lexjansen.com/pharmasug-cn/2016/PS/PharmaSUG-China-2016-PS08.pdf
https://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/Zhu-Using...
Thanks for this. I should have specified in my original post, I'm using SAS Studio so DDE is not an option unfortunately.
Why do you need to write formulas into Excel? You could do all of your calculations in SAS then just write the results to Excel. IMHO it would be far easier.
It's for a betting bot that I use. Basically, I have a SAS program that creates an Excel sheet with all the horses that meet the criteria to make them worthy of consideration for a bet. I then run the bot which identifies the odds available for each of the possible selections but will only place a bet if the odds available represent value for that particular selection.
The sheet that I am writing the formulas to uses lookups to pull data on the horses that have been backed so that information is not available to SAS at the time I run my scripts so my code dynamically creates the formulas based on the cell references that are required for the lookup.
I have tried experimenting with the excelxp.tagsets ods but am now experiencing further issues whereby one of the formulas (the lengthy one) is throwing out errors when I open the xml and the log states it is seeing it as a 'Bad Value' but I haven't been able to work out why, and certainly the formulas all work fine when I use ods html to create an xls file, I just thought it would be nice to not have the message about the file type mismatch every time I open the sheet.
Anyway, it's no biggie, I'll just carry on with what I have. As with a lot of these things, I was hoping someone had come across something similar in the past and would have a quick fix but that doesn't appear to be the case.
Thanks anyway
> I felt it was quite likely that there would be a more recent, and possibly more efficient solution.
SAS code from 40 years ago still runs fine today in the latest SAS versions, so old does not mean obsolete in the SAS world.
Having said that, maybe the newer libname XLSX or the ODS EXCEL destination would suit more, just try.
I agree that all these competing ways to write to Excel can be confusing.
> I have tried experimenting with the excelxp.tagsets ods but am now experiencing further issues
Please do contact SAS tech support if you have issues so SAS can improve their software. Just do it.
Lastly, since you have tested many options, and if you feel like it, you could contribute an updated version of the table in the paper above.
I have no doubts this would be extremely well received.
What SAS 9.4 maintenance release are you using? As @ChrisNZ says, later releases are likely to work better as far as ODS is concerned.
This new solution have similar effect like DDE (i.e. inject values to any cell of choice in an Excel template), works well for adding formulas into any Excel cells AND works well with cloud/citrix SAS environment. It may be an alternative for your problem. One-Click Report Automation - An automated and user friendly workflow for efficient, flexible, and e...
Good on you for pulling this through.
Again, feel free to contribute if you've put together a list about the various features available in the various manners SAS writes to Excel.
This is sorely lacking.
Hi @ChrisNZ ,
Thanks for your note - see updated table below. I added two new measures. Let me know if any errors.
Matrix of Export Methods and Capabilities with SAS
Consideration | “View in Excel” | PROC EXPORT | ODS CSV w/ PROC Print | ODS HTML w/ PROC Print | LIBNAME EXCEL | DDE | ODS excelXP Tagset | OCRA solution |
Automatic recognition of text strings as Excel formulas | X | X | X | X | X | X | ||
Automatic workbook creation | X | X | X | X | X | |||
Pre-formatting | X | X | X | X | ||||
Ability to export to specific Excel ranges | X | X | X | |||||
Export of multiple datasets to the same worksheet | X | X | X | |||||
Exporting variable labels instead of names | X | X | X | X | X | X | ||
Export of multiple worksheets to the same workbook | X | X | X | X | X | |||
Safety / stability | X | X | X | X | X | X | X | |
Export of image to specific Excel cell location | X |
ods excel has a formulas=on|off option! I just tried it and it works:
I'm running in EG. When I open the spreadsheet, cell A2 shows the value in Sheet2, cell B3. 🙂
data formula;
formula = '=Sheet2!B3';
output;
run;
/*****************************************************************************/
/* */
/*****************************************************************************/
ods listing close;
ods excel file = "/fmacdata/utility/npa/&ENV./Buybacks/output/Buyback_Test.xlsx"
options (autofilter='all'
embed_footnotes_once = 'on'
embedded_footnotes='on'
embed_titles_once = 'on'
embedded_titles='on'
formulas='on'
frozen_headers='on'
gridlines='on'
sheet_interval='proc'
sheet_name="Sheet1");
/*****************************************************************************/
/* */
/*****************************************************************************/
title;
proc print data=formula noobs;
run;
ods excel options(sheet_name="Sheet2");
proc print data=ref_balances noobs;
run;
ods excel close;
ods listing;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: