BookmarkSubscribeRSS Feed
Houssam
Calcite | Level 5

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

1 REPLY 1
DWilson
Pyrite | Level 9

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

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 1010 views
  • 1 like
  • 2 in conversation