【SAS】Excelの文字切れ対策

Excel文字切れ対策のめも　SASバージョン
VBAでの文字切れ対策記事(2020年11月)のあと直ぐ書きたかったが、3年以上寝かせていた。

基本的な考え方はVBAでの文字切れ対策記事を参照。
cochineal19.hatenablog.com

%let WB=ファイル名.xlsx;
%let WS=シート名;
%let FONTJ=ＭＳ ゴシック; /* 日本語フォント */
%let FONTE=Times New Roman; /* 英語フォント */
%let FONTSIZE=10; /* フォントサイズ */
%let STROW=;  /* 開始行 */
%let STCOL=;  /* 開始列 */
%let ENROW=;  /* 終了行 */
%let ENCOL=;  /* 終了列 */
   
*-- フォントサイズを１pt大きくして高さを自動調整する ;
filename exe dde "excel|system";
data _null_;
  file exe;
  put %unquote(%bquote('[workbook.activate("[&WB.]&WS.")]'));
  put %unquote(%bquote('[select("r&STROW.c&STCOL.:r&ENROW.c&ENCOL.")]'));
  put %unquote(%bquote('[font.properties("&FONTJ.",,%eval(&FONTSIZE. + 1))]'));
  put %unquote(%bquote('[font.properties("&FONTE.",,%eval(&FONTSIZE. + 1))]'));
  put %unquote(%bquote('[row.height(,"r&STROW.c&STCOL.:r&ENROW.c&ENCOL.",,3)]')); 
  put '[select("r1c1")]';
  put '[a1.r1c1(false)]';
run;
 
*-- get.cell(17)で行の高さを算出する ;
filename exe dde "excel|system";
data _null_;
  file exe;
  put '[workbook.next()]';
  put '[workbook.insert(3)]';
  put '[workbook.name(,"macro")]';
run;
 
filename xmacro1 dde "excel|macro!c1" notab;
data _null_;
  length CMD $2000.;
  file xmacro1 ;
  do i = 1 to &ENROW.;
    CMD = cats("=formula(get.cell(17,'&WS.", "'!rc), rc[1])");
    put CMD;
  end;
  put '=halt(true)';
  put '!dde_flush';
  file exe;
  put '[run("macro!r1c1:r1c1")]';
run;
 
*-- 行の高さを取り込む;
filename xmacro2 dde "excel|macro!r1c2:r%eval(&ENROW.+10)c2" notab;
data _HEIGHT1;
  infile xmacro2 ;
  length ROWC $20;
  ROW  = _n_;
  ROWC = cats("r",vvalue(ROW),"c1");
  input HEIGHT;
  HEIGHT2 = ceil(HEIGHT/15) * 15;
  if HEIGHT2 > 405 then HEIGHT2 = 405;

  if ROW >= &STROW.;
run;
 
*-- コード生成(DDE);
proc sort data=_HEIGHT1; by HEIGHT2 ROW; run;
data _HEIGHT1;
  set _HEIGHT1;
  by _HEIGHT2 ROW;
  if first.HEIGHT2 then ID=0; else ID+1;
  GRP = int(ID/10);
run;

proc transpose data=_HEIGHT1 out=_HEIGHT2;
  by HEIGHT2 GRP;
  var ROWC;
run;

data _HEIGHT3;
  set _HEIGHT2;
  length RNG1 DDE1 $20000;
  RNG1 = catx(",", of COL:);
  DDE1 = cats("put '[select(""", strip(RNG1), """)][row.height(", vvalue(HEIGHT2), ")]';");
run;

*-- DDE実行（高さ調整）;
proc sql noprint;
  select DDE1 into :DDE1 separated by ";" from _HEIGHT3;
quit;

filename exe dde "excel|system";
data _null_;
  file exe;
  put %unquote(%bquote('[workbook.activate("[&WB.]&WS.")]'));
  &DDE1.;
  put %unquote(%bquote('[select("r&STROW.c&STCOL.:r&ENROW.c&ENCOL.")]'));
  put %unquote(%bquote('[font.properties("&FONTJ.",,%eval(&FONTSIZE.))]'));
  put %unquote(%bquote('[font.properties("&FONTE.",,%eval(&FONTSIZE.))]'));
  put '[select("r1c1")]';

  %let ERR=ERR;
  put %unquote(%bquote('[&ERR.OR(false)]'));
  put %unquote(%bquote('[workbook.delete("macro")]'));
  put %unquote(%bquote('[&ERR.OR(true)]'));
run;