SAS ODS Excel with UTF-8 session encoding vs. Latin1

Question

Since running my SAS base programs under a new UTF-8 encoded environment I encounter massive performance problems when exporting data via ODS excel.

Exporting more than N records for a given dataset usually results in a frozen SAS-session and requires me to restart my client.

Here is what I use for testing, using a standard dataset and creating a small xlsx-File (<1MB):

data test03;
    set sashelp.zipcode;
    if _N_ le 3000 then output;
run;    


goptions device=png;

ods results=off;

ods excel
    file    =   "/sas_p/gridshared/ch/eg_data/regel/group_hr/export/ods_export.xlsx"
    options(    flow = "tables" start_at = "A1" embedded_titles= "yes");

proc print data=test03 label noobs;
run;

ods _all_ close;

I gradually increase _N_ (line 3), and get a corresponding increase in cpu time. However, past a certain point (usually in the 4 digit range for most datasets) the program just gets stuck.

For the example above:

_N_ = 1000 rec → 00:00:09
_N_ = 2000 rec → 00:00:18
_N_ = 3000 rec → timeout

There is none of that behaviour in the old latin1 encoded environment, where exporting all 40k records of the table above was no problem whatsoever.

Any ideas on how to tackle this problem?

How many columns is this? How big is the actual file it produces? — Joe
@Tom I am ashamed to say, that after repeatet inquiries with our IT they found out they set the memory size too low - which wasn't visible for me as a mere user. — Martin Dreher

Stu Sztukowski Stu Sztukowski · Accepted Answer · 2021-09-07T14:49:57

What if you forced the dataset encoding to be wlatin1 in your session and the ods output to also be wlatin1?

data test03(encoding='wlatin1');
    set sashelp.zipcode;
    if _N_ le 3000 then output;
run;    

filename excelout "/sas_p/gridshared/ch/eg_data/regel/group_hr/export/ods_export.xlsx"
    encoding='wlatin1'
;

goptions device=png;

ods results=off;

ods excel
    file   = excelout
    options(flow = "tables" start_at = "A1" embedded_titles= "yes")
    ;

ods excel close;

SAS ODS Excel with UTF-8 session encoding vs. Latin1

1 Answers