Excel操作 SASマクロ データの入出力

【SAS】SASデータセットをExcelファイルとして出力する（EXPORT、STREAM）

1.サンプルデータ

サンプルとして、以下2つのSASデータセットをExcelファイルとして出力してみます。

data hojin_list;
	infile datalines dlm="," dsd missover;
	input no hojin_name:$200.;
	label no="No." hojin_name="法人名";
datalines;
1,日本郵船株式会社
2,株式会社Mizkan J plus Holdings
3,公益社団法人福岡医療団
4,社団医療法人　啓愛会
5,一般社団法人 北陸地区信用金庫協会
6,兵庫県弁護士協同組合
7,学校法人　藤田学園
8,一般財団法人東北電気保安協会
9,社会医療法人蘇西厚生会
10,公益社団法人福岡医療団
;
run;

data kojin_list;
	infile datalines dlm="," dsd missover;
	input
		first_name	:$20.
		last_name	:$20.
		sex
		age
	;
datalines;
Kaori,Ito,2,22
Hideki,Tanaka,1,35
Taro,Yamada,1,30
Hiroki,Sato,1,25
Mizue,Ishii,2,54
Yuko,Koyama,2,30
Ichiro,Suzuki,1,.
Shinji,Suda,1,27
Reiko,Yamauchi,2,42
Mituru,Adachi,3,45
;
run;

hojin

kojin
 

2.展開例

以下は展開例で、引数delの値について、1段目がYで、2段目をNにしています。

%export_excel(indata=hojin_list, outfile=C:\Blog\SAS\export_excel\リスト.xlsx, sheet=法人, del=Y);
%export_excel(indata=kojin_list, outfile=C:\Blog\SAS\export_excel\リスト.xlsx, sheet=個人, del=N);

1段目をdel=Yにしているのは、同一ファイルが存在している場合に、事前に削除しておくためです。
2段目をdel=Nにしているのは、「リスト.xlsx」を削除せずに、シート「個人」を追加するためです。
 
以下のように、2シート「法人」と「個人」が存在する「リスト.xlsx」が作成されました。
export_excel
 

3.REPLACEオプションが効かない？

PROC EXPORTプロシジャのオプションに、同一ファイルが存在する場合に置換するためのREPLACEオプションがあるのですが、これを指定しても意図した置換にならないんですよね・・・。

同一ファイルが存在している状態で、REPLACEオプションで置換を試みると、ファイルが破損し、開くときに以下のようなコメントが表示されます。
export_excel_error
 
以下のように、不要なシート「回復済み_Sheet1」が生成され、意図通りの置換にならないです。
export_excel_error2
それ故、引数delを設けており、事前に削除できるようにしている、というわけです。
（以前は正しく置換できていたような・・・）
 

4.参考プログラム

以下は、参考プログラムになります。
右上のコピーボタンを押せば、プログラム全体をコピーできます。

%macro export_excel(indata=, outfile=, sheet=, label=N, del=N, fit=N);
%put --------------------------------------------------;
%put export_excel;	/*SASデータセットをExcelファイルとして出力する*/
%put &=indata;		/*データセット名を指定*/
%put &=outfile;		/*出力ファイルをフルパス指定*/
%put &=sheet;		/*任意のシート名を指定（Excelの仕様で31バイト以上は出力不可）*/
%put &=label;		/*変数名ではなくラベルを出力する場合はYを指定（既定値=N）*/
%put &=del;			/*事前に同一ファイルを削除する場合はYを指定（既定値=N）*/
%put &=fit;			/*列幅を自動調整する場合はYを指定（既定値=N）*/
%put --------------------------------------------------;

	/*同一ファイルが存在する場合に削除する*/
	%if %upcase(&del.) = Y %then %do;
		data _null_;
			rc=filename("oldfile","%superq(outfile)");
			rc=fdelete("oldfile");
		run;
	%end;
	
	%if %sysfunc(lengthn(%superq(sheet))) > 31 %then %do;
		%put WARNING: シート名は31バイト以内としてください。正しく出力されない可能性があります。&=sheet;
	%end;

	proc export data=&indata.
				outfile="%superq(outfile)"
				dbms=xlsx
				%if %upcase(&label.) = Y %then %do; label %end;
				replace
				;
		sheet="&sheet."n;
	run;

	/*.bakファイルが存在する場合は削除*/
	data _null_;
		rc=filename("bakfile","%superq(outfile).bak");
		rc=fdelete("bakfile");
	run;

	/*列幅の自動調整（エクセルファイルが破損、PW設定などされている場合、エラーとなるため注意）*/
	%if %upcase(&fit.)=Y %then %do;
		%let path=%substr(%superq(outfile), 1, %length(%superq(outfile))-%length(%scan(%superq(outfile), -1, '\'))-1);
		%if %substr(%superq(path), %length(%superq(path))) = \ %then %do;
			%put ERROR: outfileのバックスラッシュの入力が不正です。&=outfile;
			%abort;
		%end;

		option noxwait xsync;
		filename outpath "%superq(path)";
		data _null_;
			call symputx("outpath",pathname("outpath"));
			text="'"||tranwrd(strip(pathname("outpath")),'','" "')||'\エクセル設定用.vbs'||"'";
			call symputx("xcommand",text);
		run;

		filename vbs "%superq(path)\エクセル設定用.vbs" encoding="sjis";
		proc stream outfile=vbs resetdelim="br"; begin
		Set ex=CreateObject("Excel.Application") br newline;
		ex.Visible=True br newline;
		ex.Workbooks.Open("%superq(outfile)")  br newline;
		ex.DisplayAlerts=False br newline;
		ex.ActiveWorkbook.Sheets("%superq(sheet)").Columns.AutoFit br newline;
		ex.ActiveWorkbook.SaveAs "%superq(outfile)" br newline;
		ex.Quit
		;;;;
		filename vbs clear;

		data _null_;
			call system(&xcommand.);
			rc=filename("vbs","%superq(path)\エクセル設定用.vbs");
			rc=fdelete("vbs");
		run;
		filename outpath clear;
		option noxsync;
	%end;

%mend export_excel;