Problem Note 65468: A Microsoft Excel formula within a text string appears as text only in an Excel file if the string is in a DATA step with the ODS Excel destination
 |  |  |  |
Formulas within a string that are generated by the DATA step or the ODS Report Writing Interface and that use the ODS Excel destination do not appear correctly in the resulting Excel file. When the length of the string including the formula is greater than 61 characters in the code, the entire string appears as text only, rather than a resolved formula, in the Excel file.
The syntax shown below would introduce this problem:
data one;
var1='=CONCATENATE("really long string that calculates 1+1=", SUM(1,1))';
run;
ods excel;
var1='=CONCATENATE("really long string that calculates 1+1=", SUM(1,1))';
run;
ods excel;
The workaround is to add the string containing the formula through the PRINT procedure or the REPORT procedure.
Here is an example of a workaround with PROC PRINT:
proc print data=one;
run;
run;
proc print data=one;
var var1 /style(data)={tagattr='formula:=CONCATENATE("really long string that calculates 1+1=", SUM(1,1))'};
run;
ods excel close;
var var1 /style(data)={tagattr='formula:=CONCATENATE("really long string that calculates 1+1=", SUM(1,1))'};
run;
ods excel close;
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | Base SAS | z/OS | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 |
| z/OS 64-bit | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Microsoft® Windows® for x64 | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Microsoft Windows 8 Enterprise 32-bit | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Microsoft Windows 8 Enterprise x64 | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Microsoft Windows 8 Pro 32-bit | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Microsoft Windows 8 Pro x64 | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Microsoft Windows 8.1 Enterprise 32-bit | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Microsoft Windows 8.1 Enterprise x64 | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Microsoft Windows 8.1 Pro 32-bit | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Microsoft Windows 8.1 Pro x64 | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Microsoft Windows 10 | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2008 | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows Server 2008 R2 | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows Server 2008 for x64 | 9.4_M3 | 9.4 TS1M3 | ||||
| Microsoft Windows Server 2012 Datacenter | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2012 R2 Datacenter | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2012 R2 Std | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Microsoft Windows Server 2012 Std | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Windows 7 Enterprise 32 bit | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Windows 7 Enterprise x64 | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Windows 7 Home Premium 32 bit | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Windows 7 Home Premium x64 | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Windows 7 Professional 32 bit | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Windows 7 Professional x64 | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Windows 7 Ultimate 32 bit | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Windows 7 Ultimate x64 | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| 64-bit Enabled AIX | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| 64-bit Enabled Solaris | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| HP-UX IPF | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Linux for x64 | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Solaris for x64 | 9.4_M3 | 9.4_M7 | 9.4 TS1M3 | 9.4 TS1M7 | ||
| Type: | Problem Note |
| Priority: | medium |
| Date Modified: | 2020-02-03 09:35:05 |
| Date Created: | 2020-01-28 16:20:54 |