古いデータセットを新しいデータセットとマージすることで更新してExcel出力する際に、更新された項目のセルの背景色を塗りつぶしたい

[sasada]

医薬品メーカーにて、治験業務に携わっている者です。

DDEを用いたExcel操作についてご質問させて頂きたく思います。

SASを用いて有害事象に関する進捗管理表(Excel)を月次作成、更新しております。

概ね、下記のような処理手順です。

①前月の進捗管理表（Excel）を取込み、SASデータセット化する

②今月分のデータ（SASデータセット）を取込み、前月分とマージすることで更新する

③更新したSASデータセットをExcelのテンプレートに出力する

なお、マージして出力する部分のコードは以下の通りです。

  data work.new_trck;
   merge work.last_trck (in=last)　/* <--- 前月の進捗管理表（Excelから取込み済み）*/
         work.info_code_flg(in=new)/* <--- 今月のデータ*/
              ;
    by ID SEQNO; /* <--- 被験者番号と有害事象の管理連番をキーとする*/
  run;

/*出力・保存 ***************/
/*** 出力行数をマクロ変数に格納 ***************/
data _null_ ;
  call symput("outobs",strip(put((_obs+4),best.))) ;
  set work.new_trck nobs=_obs ;
run;

x """&TMPPATH.\&TMPFILE.""" ;  <--- テンプレートのExcelファイルを開く*/

%sleep3/* <--- スリープ処理のマクロ*/

filename outxls dde " Excel|[&TMPFILE.]Sheet1!R5C1:R&outobs.C41"  lrecl=32000 ;

/*** データ内容の出力 ***************/
data _null_ ;
  set work.new_trck;
  file outxls dsd dlm="09"x notab;
  put  ID Site AGE SEX VISITDT SEQNO AETERM LLT_ENG PT_ENG AESTDTC AESTDTC_DN AEENDTC AEENDTC_DN 
        AEOUT AESEV AEACN AESER AESERDTC AESERTIM AESDTH AESLIFE AESHOSP AESDISAB AESCONG AESMIE 
        col28 col29 col30 col31 col32 col33 col34 col35 col36 col37 col38 col39 col40 col41 
        ;
run ;

data _null_ ;
  file cmd ;
  put "[select(""r5c2:r&outobs.c41"")]";
  put '[border(2,1,1,1,1)]';
  put '[select("r1c1")]';
  put "[save.as(""&OUTPATH.\&OUTFILE."")]"; 
  put '[error(false)]' ;  
  put '[close("' "&OUTFILE." '")]';
run ;

この処理に加え、下略図のように値が更新された項目のみ背景色で塗りつぶして分かりやすくしたいと考えています。

▼前月分

ID	SEQ No.	Adverse Event	Start Date	End Date	Outcome
X2205	1	Vomiting	2020/12/1	2020/12/5	Recovering/resolving
X2205	2	headadhe	2020/12/11	2020/12/15	Recovered/resolved

▼今月分

ID	SEQ No.	Adverse Event	Start Date	End Date	Outcome
X1111	1	twitching masticatory	2021/2/2	2021/2/12	Recovered/resolved
X2205	1	Vomiting	2020/12/1	2021/2/5	Recovered/resolved
X2205	2	headache	2020/12/11	2020/12/15	Recovered/resolved
X2522	1	Low back pain	2021/2/4	2021/2/14	Recovered/resolved

▼出力結果

ID	SEQ No.	Adverse Event	Start Date	End Date	Outcome
X1111	1	twitching masticatory	2021/2/2	2021/2/12	Recovered/resolved
X2205	1	Vomiting	2020/12/1	2021/2/5	Recovered/resolved
X2205	2	headache	2020/12/11	2020/12/15	Recovered/resolved
X2522	1	Low back pain	2021/2/4	2021/2/14	Recovered/resolved

※実際は赤字のセルを背景色塗りつぶし

上記コードを改変するかたちで何かよい方法はございますでしょうか。

また、上記コードをベースにしない、DDEを用いない等でよりうまく処理できる方法があるようでしたら、

そちらでも結構ですので、ご教示頂ければ幸いです。

どうぞよろしくお願い致します。

[japelin · Accepted solution]

思いつくのは4つです。

1. 出力した後にエクセルマクロで処理する→フラグは必要ない。ExcelのVBAでIDとSeqnoからチェックすればよい。ただし以前の出力ファイルが必要（もしくは別シートに前回分を出力し、マクロ処理の最後にシートを削除する）。
2. 変更フラグを持ったSASデータセットを作成し、フラグがONならDDEで塗りつぶすdataステップを別に実行する
3. SASデータセットに変更フラグをもたせておき、フラグも出力する。Excelテンプレートには条件付き書式を指定しておき、フラグがONなら条件付き書式で色を変更する。
4. Excelではなく、proc reportで出力htmlをコントロールする（こちらもSASデータセットにフラグは必要）

フラグを全変数分もたせるのは面倒ですが、compareプロシージャ使えば作るのは簡単でしょう。

proc compare base=a compare=b out=x OUTNOEQUAL;
run;

2の色を変更するプログラムサンプルは以下のとおりです。参考にしてください。

data _null_;
  file cmd;
  length x $100;
  do i=1 to 56;
    x=cats('[select("R1C',i,'")]');
    put x;
    put "[patterns(0,0,3,true)]";
    x=cats('[patterns(1,0,',i,',true)]');/*色番号を指定*/
    put x;
  end;
run; 

4についてはSASのFAQが参考になると思います。

REPORTプロシジャで、1行おきに背景色を変える

1のエクセルマクロ、3の条件付き書式についてはここでの説明は省きます。

[sasada]

早速ご教示頂きありがとうございます。

compareプロシージャのデータセット出力を使ったことがなく、2データセット間の不一致フラグを持ったデータセットを作成できる

という発想がありませんでした。

今回は2.の案にて解決いたしましたが、その他にも複数のご提案を頂き、丁寧なご回答ありがとうございました。