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