Writing Excel formulas in SAS using ods

[robulon]

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

[Sajid01]

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... 

[robulon]

Thanks for this. I should have specified in my original post, I'm using SAS Studio so DDE is not an option unfortunately.

[ChrisNZ]

Did you search for this topic? So many papers, such as this and this.

[robulon]

Yes, I did thanks and I did look at both of these papers, however due to the fact that they were both over ten years old, I felt it was quite likely that there would be a more recent, and possibly more efficient solution.

Thanks for the taking the time to provide the links though.

[SASKiwi]

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.

[robulon]

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

[ChrisNZ]

>  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.

[SASKiwi]

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.

[Bravez]

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...

[ChrisNZ]

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.

[Bravez]

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

[PatMalarkey]

ods excel has a formulas=on|off option!  I just tried it and it works:

SAS Documentation 

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;