Does SAS ODS support Excel ArrayFormulas (CSE-Formulas)? If so, what is the correct syntax?
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 (The complexity of the formula does not matter):
=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)))))}
