Hi,
I am not sure about sending them in as variable values.
This is the way things work with the EXCELXP tagset.
This is the part of the help file relating to the FORMULAS sub option for the EXCELXP tagset :
Formulas: Default Value 'yes' Values: yes, no, on, off. By default, data values that start with an '=' will become formulas instead of cell values. This behavior can be turned off by setting this option to 'no'. Excel only understands relative column references in it's XML. A formula like sum(C2,C3) or A2+B3 will not work. An equivalent might be sum(R[-2]C,R[-1]C) or RC[-2]+RC[-1]. See the Proc Print example under Default_Column_Width.
With this program, the CALC1 is correctly constructed (while the CALC2 is not, as said by the documentation) :
data class;
set sashelp.class;
calc1="=RC[-1]/RC[-2]";
length calc2 $ 8 ;
calc2="=F"||cats(_n_+1)||"/E"||cats(_n_+1);
run;
ODS tagsets.excelxp file="c:/intro_sas/ods/ex3.xml";
proc print data=class;
run;
ODS tagsets.excelxp close ;
This FORMULAS sub-option is described this way in the ODS EXCEL help:
(FORMULAS= 'OFF' | 'ON')
specifies if data values that begin with an '=' become formulas or cell values.
ON
data values that begin with an '=' become formulas.
Alias
YES
OFF
data values that begin with an '=' become cell values.
well... since the sub option is having the same name between ODS EXCEL and the EXCELXP tagset, I expected ODS EXCEL to act with formulas the same way the EXCELXP tagset did.
This paper: https://support.sas.com/resources/papers/proceedings16/SAS5642-2016.pdf (page 10), seems to confirm that A1 and R1C1 notations are accepted.
Since, with ODS EXCEL, a formula in A1 notation is accepted,
since, with the EXCELXP tagset, R1C1 notations were accepted
I wonder why R1C1 notations are not accepted with ODS EXCEL the way they were accepted by the EXCELXP tagset.
and yes, TAGATTR could do this job... but... between you and me, would you tell me that tagattr is very easy to use ? 😉
best regards
Sébastien
... View more