Does SAS ODS support Excel Array Formulas (CSE-Formulas)?

[Houssam]

Hey guys I hope you are doing marvelously well,

Is there a way to write Excel ArrayFormulas in ODS? To create legacy ArrayFormulas (prior to 2018), you need to press Ctrl+Shift+Enter in Excel. I need to do this in SAS ODS however for automatic reporting. I am using SAS Data Integration Studio 4.8 and Microsoft Office 2010.

What I have so far:

[tagattr= "formula: =COUNT(1/FREQUENCY(IF((SUBTOTAL(3,OFFSET(R[+4]C[-15],ROW(R[+4]C[-15]:R[+&anzahlZeilen.]C[-15])-ROW(R[+4]C[-15]),0))=1) * (R[+4]C[-15]:R[+&anzahlZeilen.]C[-15]<>""""), MATCH(R[+4]C[-15]:R[+&anzahlZeilen.]C[-15],R[+4]C[-15]:R[+&anzahlZeilen.]C[-15],0)), ROW(INDIRECT(""1:""&COUNTA(R[+4]C[-15]:R[+&anzahlZeilen.]C[-15])))))

This results in the following Excel Formula: 

=COUNT(1/FREQUENCY(IF((SUBTOTAL(3;OFFSET(C10;ROW(C10:C4293)-ROW(C10);0))=1) * (C10:C4293<>""); MATCH(C10:C4293;C10:C4293;0)); ROW(INDIRECT("1:"&COUNTA(C10:C4293)))))

The results is I am looking for however is (same as above but encapsulated with {}):

{=ANZAHL(1/HÄUFIGKEIT(WENN((TEILERGEBNIS(3;BEREICH.VERSCHIEBEN(C10;ZEILE(C10:C4293)-ZEILE(C10);0))=1) * (C10:C4293<>""); VERGLEICH(C10:C4293;C10:C4293;0)); ZEILE(INDIREKT("1:"&ANZAHL2(C10:C4293)))))}

Any solutions or workarounds?

Thank you in advance.

Houssam

[DWilson]

I don't know of a way to have ODS embed excel formulas into an excel file.

As a workaround, you could use R and a package like openxlsx or xlconnect and SAS IML to add Excel functions to an excel spreadsheet.

To improve the functionality of creating excel files from within SAS, I create the data to be placed into an excel file using SAS and then call IML from within SAS to invoke R, pass the data to R and any commands associated with openxlsx or xlconnect to create and modify the excel file I want to create.

This paper provides details on how we do that:

https://analytics.ncsu.edu/sesug/2017/SESUG2017_Paper-109_Final_PDF.pdf