Overview
Data Step
Data Aggregation
Data Visualization
Proc SQL
Variable Analysis
Macro
Model Regression
Variable Selection
Model Assessment

Xuất kết quả ra Excel

Cách thức xuất kết quả (dữ liệu, đồ thị) ra Excel.

Thanh Tra
Written by

Mục lục

ODS Excel

Để output kết quả PROC PRINT, PROC SGPLOT, … ra file Excel, ta sử dụng ODS Excel như sau:

ODS EXCEL FILE="D:\SASWORKSPACE\MONITORING.XLSX"  TITLE="" STYLE=TRANT6 
AUTHOR='trant6@vpbank.com.vn' NOGTITLE;

/*-----------------SHEET1---------------*/
ODS EXCEL  OPTIONS(
EMBEDDED_TITLES='ON'
SHEET_INTERVAL="NONE"
SHEET_NAME="Population Stability Report"
);
ODS GRAPHICS / HEIGHT=900 WIDTH=1200 BORDER;

/*---------CONTENT SHEET 1 HERE -----*/
/*-----------------SHEET2---------------*/
ODS EXCEL OPTIONS(SHEET_INTERVAL="TABLE");
ODS EXCLUDE ALL;

DATA _NULL_;
FILE PRINT;
PUT _ALL_;
RUN;

ODS SELECT ALL;
ODS EXCEL  OPTIONS(
SHEET_INTERVAL="NONE"
SHEET_NAME="Full Characteristic Analysis"
EMBEDDED_TITLES='ON'
);

/*---------CONTENT SHEET2 HERE -----*/
ODS EXCEL CLOSE;

Trong đó:

  • FILE Vị trí file cần lưu.
  • TITLE/ AUTHOR Title/author của file Excel.
  • STYLE style của file Excel, được đề cập như ở dưới đây.
  • SHEET_INTERVAL Mỗi report một trang hay không.
  • SHEET_NAME Tên của sheet.

Ví dụ như sau:

Trước hết, ta sẽ tạo data:

PROC SQL NOPRINT;
	CREATE TABLE TEMP00 AS 
		SELECT MAKE, ORIGIN, COUNT(*) AS NUM, MEAN(INVOICE) AS  INVOICE, MEAN(MPG_CITY) AS MPG_CITY
			FROM SASHELP.CARS
				GROUP BY MAKE, ORIGIN
					HAVING (CALCULATED NUM)>10
						ORDER BY (CALCULATED INVOICE);
QUIT;

PROC SQL NOPRINT;
	CREATE TABLE TEMP01 AS 
		SELECT MAKE, ORIGIN, COUNT(*) AS NUM, MEAN(INVOICE) AS  INVOICE, MEAN(MPG_CITY) AS MPG_CITY
			FROM SASHELP.CARS
				GROUP BY MAKE, ORIGIN
					HAVING (CALCULATED NUM)>10
						ORDER BY ORIGIN, (CALCULATED INVOICE);
QUIT;

Để output ra file Excel, ta sử dụng cú pháp như sau:

ODS EXCEL FILE="&LOCATION\SAS to Excel.xlsx"  TITLE="Example"
	STYLE=HTMLBLUE AUTHOR='trant6@vpbank.com.vn' NOGTITLE;

/*-----------------SHEET1---------------*/
ODS EXCEL  OPTIONS(
	EMBEDDED_TITLES='ON'
	SHEET_INTERVAL="NONE"
	SHEET_NAME="Coarse Binning"
	);
ODS GRAPHICS / HEIGHT=450 WIDTH=600 BORDER;
OPTIONS MISSING='';



PROC REPORT DATA=TEMP00 NOWD 
	STYLE(HEADER)=[FONT_WEIGHT=BOLD BACKGROUND=CX159A58 FOREGROUND=WHITE BORDERCOLOR=WHITE]
	STYLE(COLUMN)=[BORDERCOLOR=WHITE];
	COLUMN  MAKE ORIGIN	NUM	INVOICE	MPG_CITY;
	DEFINE MAKE / "Make" DISPLAY;
	DEFINE ORIGIN / "Origin" DISPLAY;
	DEFINE NUM / "Number" DISPLAY FORMAT=NLNUM20.;
	DEFINE INVOICE / "Mean Invoice" DISPLAY FORMAT=NLNUM20.;
	DEFINE MPG_CITY / "Mean MPG City" DISPLAY FORMAT=10.3;
RUN;

PROC SGPLOT DATA=TEMP00;
	VBAR MAKE/ RESPONSE=INVOICE NOOUTLINE  MISSING ;
	VLINE MAKE/ RESPONSE=MPG_CITY Y2AXIS LINEATTRS=(COLOR=CX9CBA5F PATTERN=SOLID THICKNESS=2PX)  
		MARKERATTRS=(SYMBOL=CIRCLEFILLED COLOR=CX9CBA5F) MARKERS MISSING;
	TITLE "Summary data by Make";
	Y2AXIS LABEL="Mean MPG City";
	YAXIS LABEL="Mean Invoice";
	XAXIS LABEL="Make" DISCRETEORDER=DATA;
	LABEL INVOICE="Mean Invoice" MPG_CITY="Mean MPG City";
RUN;

/*-----------------SHEET2---------------*/
ODS EXCEL OPTIONS(SHEET_INTERVAL="TABLE");
ODS EXCLUDE ALL;

DATA _NULL_;
	FILE PRINT;
	PUT _ALL_;
RUN;

ODS SELECT ALL;
ODS EXCEL  OPTIONS(
	SHEET_INTERVAL="NONE"
	SHEET_NAME="Fine Binning"
	EMBEDDED_TITLES='ON'
	);
OPTIONS MISSING='';

PROC FORMAT;
	VALUE $FTM_COLOR  
		'Asia'='STYLE=[BACKGROUND=CX66BD7D]'
		'Europe'='STYLE=[BACKGROUND=CXFEEA8A]'
		'USA'='STYLE=[BACKGROUND=CXF66A6E]'
	;
RUN;

PROC REPORT DATA=TEMP01 NOWD 
	STYLE(HEADER)=[FONT_WEIGHT=BOLD BACKGROUND=CX159A58 FOREGROUND=WHITE BORDERCOLOR=WHITE]
	STYLE(COLUMN)=[BORDERCOLOR=WHITE];
	COLUMN  MAKE ORIGIN	NUM	INVOICE	MPG_CITY;
	DEFINE MAKE / "Make" DISPLAY;
	DEFINE ORIGIN / "Origin" DISPLAY;
	DEFINE NUM / "Number" DISPLAY FORMAT=NLNUM20.;
	DEFINE INVOICE / "Mean Invoice" DISPLAY FORMAT=NLNUM20.;
	DEFINE MPG_CITY / "Mean MPG City" DISPLAY FORMAT=10.3;
	COMPUTE ORIGIN;
		CALL DEFINE(_ROW_,"STYLE",PUT(ORIGIN,$FTM_COLOR.));
	ENDCOMP;
RUN;

PROC SGPLOT DATA=TEMP01;
	VBAR MAKE/ RESPONSE=INVOICE GROUP=ORIGIN NOOUTLINE  MISSING ;
	VLINE MAKE/ RESPONSE=MPG_CITY GROUP=ORIGIN  Y2AXIS LINEATTRS=(COLOR=CX9CBA5F PATTERN=SOLID THICKNESS=2PX)  
		MARKERATTRS=(SYMBOL=CIRCLEFILLED COLOR=CX9CBA5F) MARKERS MISSING;
	TITLE "Summary data by Make";
	Y2AXIS LABEL="Mean MPG City";
	YAXIS LABEL="Mean Invoice";
	XAXIS LABEL="Make" DISCRETEORDER=DATA;
	LABEL INVOICE="Mean Invoice" MPG_CITY="Mean MPG City";
RUN;


OPTIONS MISSING=.;
ODS EXCEL CLOSE;

Kết quả nhận được là file Excel như sau:

Template

Kết quả output ra file excel sẽ có template mặc định như của SAS. Để chỉnh sửa template (font chữ, kích thước chữ, …) của file Excel, ta sử dụng Proc Template như sau:

PROC TEMPLATE;
	DEFINE STYLE STYLES.TRANT6;
		PARENT = STYLES.HTMLBLUE;
		STYLE DATA FROM DATA/
			BACKGROUNDCOLOR=WHITE
			FONT=("ARIAL", 12PT)
			BORDERCOLOR=WHITE;
		STYLE HEADER FROM HEADER /
			BACKGROUNDCOLOR=CX159A58
			COLOR=CXFFFFFF
			FONT=("ARIAL", 12PT)
			FONTWEIGHT=BOLD
			TEXTALIGN=CENTER
			VERTICALALIGN=CENTER
			BORDERCOLOR=WHITE;
		STYLE CONTENTTITLE FROM CONTENTTITLE /
			BACKGROUNDCOLOR=CX159A58
			COLOR=CXFFFFFF
			FONT=("ARIAL", 12PT)
			FONTWEIGHT=BOLD
			TEXTALIGN=CENTER
			VERTICALALIGN=CENTER
			BORDERCOLOR=WHITE;
		STYLE SYSTEMTITLE FROM SYSTEMTITLE/
			BACKGROUNDCOLOR=CX159A58
			COLOR=CXFFFFFF
			FONT=("ARIAL", 12PT)
			FONTWEIGHT=BOLD
			TEXTALIGN=CENTER
			VERTICALALIGN=CENTER
			BORDERCOLOR=WHITE;
		STYLE ROWHEADER FROM ROWHEADER /
			BACKGROUNDCOLOR=WHITE
			COLOR=CX159A58
			FONT=("ARIAL", 12PT)
			FONTWEIGHT=BOLD
			TEXTALIGN=CENTER
			VERTICALALIGN=CENTER
			BORDERCOLOR=WHITE;
		STYLE GRAPHFONTS FROM GRAPHFONTS /
			'GRAPHDATAFONT' = ("ARIAL",7PT)
			'GRAPHLABELFONT' = ("ARIAL",10PT)
			'GRAPHFOOTNOTEFONT' = ("ARIAL",10PT);
		STYLE GRAPHDATA1 FROM GRAPHDATA1 /
			COLOR=CX5182BB;
		STYLE GRAPHDATA2 FROM GRAPHDATA2 /
			COLOR=CXBE5150;
		STYLE GRAPHDATA3 FROM GRAPHDATA3 /
			COLOR=CX9CBA5F;
		STYLE GRAPHDATA4 FROM GRAPHDATA4 /
			COLOR=CX8066A0;
		STYLE GRAPHDATA5 FROM GRAPHDATA5 /
			COLOR=CX50ACC4;
		STYLE GRAPHDATA6 FROM GRAPHDATA6 /
			COLOR=CXD98445;
		STYLE GRAPHDATA7 FROM GRAPHDATA7 /
			COLOR=CX94AACE;
		STYLE GRAPHGRIDLINES FROM GRAPHGRIDLINES/
			CONTRASTCOLOR=CX868686;
	END;
RUN;

Các giá trị có thể chỉnh sửa format bao gồm:

  • DATA
  • HEADER
  • CONTENTTITLE,
  • … Với mỗi phần, có thể chỉnh sửa các giá trị:
  • COLOR, FONT, FONTWEIGHT
  • ALIGN, VERTICALALIGN
  • BACKGROUND,
  • …

Sau đó, ta sử dụng template bằng tùy chọn STYLE=TRANT6 như sau:

ODS EXCEL FILE="&LOCATION\SAS to Excel.xlsx"  TITLE="Example"
	STYLE=TRANT6 AUTHOR='trant6@vpbank.com.vn' NOGTITLE;

/*-----------------SHEET1---------------*/
ODS EXCEL  OPTIONS(
	EMBEDDED_TITLES='ON'
	SHEET_INTERVAL="NONE"
	SHEET_NAME="Coarse Binning"
	);
ODS GRAPHICS / HEIGHT=450 WIDTH=600 BORDER;
OPTIONS MISSING='';



PROC REPORT DATA=TEMP00 NOWD 
	STYLE(HEADER)=[FONT_WEIGHT=BOLD BACKGROUND=CX159A58 FOREGROUND=WHITE BORDERCOLOR=WHITE]
	STYLE(COLUMN)=[BORDERCOLOR=WHITE];
	COLUMN  MAKE ORIGIN	NUM	INVOICE	MPG_CITY;
	DEFINE MAKE / "Make" DISPLAY;
	DEFINE ORIGIN / "Origin" DISPLAY;
	DEFINE NUM / "Number" DISPLAY FORMAT=NLNUM20.;
	DEFINE INVOICE / "Mean Invoice" DISPLAY FORMAT=NLNUM20.;
	DEFINE MPG_CITY / "Mean MPG City" DISPLAY FORMAT=10.3;
RUN;

PROC SGPLOT DATA=TEMP00;
	VBAR MAKE/ RESPONSE=INVOICE NOOUTLINE  MISSING ;
	VLINE MAKE/ RESPONSE=MPG_CITY Y2AXIS LINEATTRS=(COLOR=CX9CBA5F PATTERN=SOLID THICKNESS=2PX)  
		MARKERATTRS=(SYMBOL=CIRCLEFILLED COLOR=CX9CBA5F) MARKERS MISSING;
	TITLE "Summary data by Make";
	Y2AXIS LABEL="Mean MPG City";
	YAXIS LABEL="Mean Invoice";
	XAXIS LABEL="Make" DISCRETEORDER=DATA;
	LABEL INVOICE="Mean Invoice" MPG_CITY="Mean MPG City";
RUN;

/*-----------------SHEET2---------------*/
ODS EXCEL OPTIONS(SHEET_INTERVAL="TABLE");
ODS EXCLUDE ALL;

DATA _NULL_;
	FILE PRINT;
	PUT _ALL_;
RUN;

ODS SELECT ALL;
ODS EXCEL  OPTIONS(
	SHEET_INTERVAL="NONE"
	SHEET_NAME="Fine Binning"
	EMBEDDED_TITLES='ON'
	);
OPTIONS MISSING='';

PROC FORMAT;
	VALUE $FTM_COLOR  
		'Asia'='STYLE=[BACKGROUND=CX66BD7D]'
		'Europe'='STYLE=[BACKGROUND=CXFEEA8A]'
		'USA'='STYLE=[BACKGROUND=CXF66A6E]'
	;
RUN;

PROC REPORT DATA=TEMP01 NOWD 
	STYLE(HEADER)=[FONT_WEIGHT=BOLD BACKGROUND=CX159A58 FOREGROUND=WHITE BORDERCOLOR=WHITE]
	STYLE(COLUMN)=[BORDERCOLOR=WHITE];
	COLUMN  MAKE ORIGIN	NUM	INVOICE	MPG_CITY;
	DEFINE MAKE / "Make" DISPLAY;
	DEFINE ORIGIN / "Origin" DISPLAY;
	DEFINE NUM / "Number" DISPLAY FORMAT=NLNUM20.;
	DEFINE INVOICE / "Mean Invoice" DISPLAY FORMAT=NLNUM20.;
	DEFINE MPG_CITY / "Mean MPG City" DISPLAY FORMAT=10.3;
	COMPUTE ORIGIN;
		CALL DEFINE(_ROW_,"STYLE",PUT(ORIGIN,$FTM_COLOR.));
	ENDCOMP;
RUN;

PROC SGPLOT DATA=TEMP01;
	VBAR MAKE/ RESPONSE=INVOICE GROUP=ORIGIN NOOUTLINE  MISSING ;
	VLINE MAKE/ RESPONSE=MPG_CITY GROUP=ORIGIN  Y2AXIS LINEATTRS=(COLOR=CX9CBA5F PATTERN=SOLID THICKNESS=2PX)  
		MARKERATTRS=(SYMBOL=CIRCLEFILLED COLOR=CX9CBA5F) MARKERS MISSING;
	TITLE "Summary data by Make";
	Y2AXIS LABEL="Mean MPG City";
	YAXIS LABEL="Mean Invoice";
	XAXIS LABEL="Make" DISCRETEORDER=DATA;
	LABEL INVOICE="Mean Invoice" MPG_CITY="Mean MPG City";
RUN;


OPTIONS MISSING=.;
ODS EXCEL CLOSE;

Kết quả nhận được như sau (chú ý title của các report).

Report native Excel

Với các file excel ở trên, ta nhận thấy một số nhược điểm như sau:

  • Các đồ thị ở dạng ảnh. Các file này không tiện dụng trong nhiều trường hợp (làm slide, zoom, đổi màu, …).
  • Định dạng của cột D ở dạng text. Nguyên nhân là do ta đã thêm các dấu phảy ngăn cách hàng nghìn vào các giá trị của cột.

Do đó, ta mong muốn một số yêu cầu cho việc này như sau:

  • Các đồ thị phải ở định dạng của Excel.
  • Format của các cột phải chính xác.
  • Thêm công thức vào vào sheet của File Excel.
  • Merge một số cột

Định dạng cho file Excel

Để điều chỉnh định dạng các cột khi output excel, ta cần thêm trong PROC REPORT.

DEFINE PRINCIPAL_AMT / STYLE={TAGATTR='FORMAT:###,###,###,###'};
DEFINE INTEREST_AMT / STYLE={TAGATTR='FORMAT:###,###,###,###'};

Thêm công thức

Để hiện công thức khi output ra file Excel, dùng FORMULA trong tùy chọn STYLE. Ví dụ:

COMPUTE N;
N = 0;
CALL DEFINE(_COL_,'STYLE','STYLE={TAGATTR=" FORMULA:RC[-5]-DATE(2018, 12, 31)"}');
ENDCOMP;
Trong đó RC[-5] thể hiện lùi 5 vị trí trước cột hiện tại.

Merge các dòng

Ví dụ cho cả ba thao tác trên như sau:

Đồ thị Excel

Chú ý rằng việc vẽ các đồ thị “thuần Excel” tốn khá nhiều công sức, do đó chỉ sử dụng khi báo cáo thực sự cần thiết. Hiện tại, SAS đang có công cụ đẩy ra chart excel nhưng đang ở phiên bản thử nghiệm và rất khó sử dụng. Do đó, ta dùng các bước như sau để có được report thuần Excel:

  • Dùng ODS Excel để đẩy data ra file excel. Lưu ý các data này “phải ở dạng excel” để có thể dễ dàng vẽ hình bằng VBA.
  • Thiết kể VBA script để vẽ hình.
  • Cài đặt macro trên Excel.
  • Dùng SAS để gọi VBA Script áp dụng vào file Excel

Code .bas có nội dung như sau:

Dim Input_Excel, output_excel, bas_code_path, vba_module, vba_code, objxl, objwk, vbCom, myMod

Input_Excel 	= WScript.Arguments(0) 'Full path and Input file name
output_excel 	= WScript.Arguments(1) 'Full path and Output file name
bas_code_path 	= WScript.Arguments(2) 'Full path Location of .bas file
vba_module	 	= WScript.Arguments(3) 'VBA module (without the .bas)
vba_code		= WScript.Arguments(4) 'VBA subroutine name to execute

set objxl 		= CreateObject("Excel.Application") 'Start Excel
set objwk 		= objxl.Workbooks.Open(Input_Excel) 'Open the input file

'Activate special software
set vbCom 		= objxl.ActiveWorkbook.VBProject.VBComponents

objxl.DisplayAlerts = wdAlertsNone 'Turn off error messages

if vba_module <> "" then
	'Import Report level VBA module
	vbCom.Import ("" & bas_code_path & vba_module & ".bas")
	'Import Common Routine VBA module
	objxl.Run "" & vba_module & "." & vba_code & "" 'Run VBA routine
	'Remove VBA modules
	Set myMod = objxl.ActiveWorkbook.VBProject.VBComponents("" & vba_module & "")
	objwk.VBProject.VBComponents.Remove myMod

end if

'Save as Excel workbook
if Input_Excel <> output_excel then objxl.ActiveWorkbook.SaveAs output_excel, 51, , , , False
'xlExcel8
if Input_Excel = output_excel then objxl.ActiveWorkbook.Save
objxl.Quit
set objxl = nothing
set objwk = nothing

Tiếp đó, ta sẽ cài đặt macro trên Excel để có thể sử dụng macro. Thực hiện như sau: Mở excel/ options/ Trust Center/ Trus Center Settings…

Cài đặt Excel
Cài đặt Excel
  • Tại tab Macro Settings, chọn Enable VBA Macros (…)
  • Tại tab ActiveX Settings, chọn Enable controls without restrictions …

Chọn OK để lưu lại.

Để gọi code .bas từ SAS, ta dùng code:

%LET VBS_CODE = D:\SASWORKSPACE\VBS_EXECUTE_SCRIPT.VBS; /* VBS SUBROUTINE NAME TO EXECUTE */
%LET INPUT_EXCEL = D:\SASWORKSPACE\MONITORING.XLSX; /* FULL PATH AND INPUT FILE NAME */
%LET OUTPUT_EXCEL = D:\SASWORKSPACE\MONITORING_&RECDAY..XLSX; /* FULL PATH AND OUTPUT FILE NAME */
%LET BAS_CODE_PATH = D:\SASWORKSPACE\; /* FULL PATH LOCATION OF BAS FILE*/
%LET VBA_MODULE = MONITORING; /* VBA PATH AND MODULE NAME (WITHOUT THE BAS)*/
%LET VBA_CODE = MONITORING; /* VBA SUBROUTINE NAME TO EXECUTE ;*/
X "'&VBS_CODE.' ""&INPUT_EXCEL"" ""&OUTPUT_EXCEL"" ""&BAS_CODE_PATH"" ""&VBA_MODULE"" ""&VBA_CODE"" ";

Trong đó:

  • VBS_CODE là file có sẵn, có chức năng mở file Excel, áp dụng macro và lưu file Excel.
  • INPUT_EXCEL File output từ SAS, chỉ chứa các dữ liệu để vẽ đồ thị.
  • OUTPUT_EXCEL: File sau khi đã chạy VBA.
  • BAS_CODE_PATH: Đường dẫn đến file VBA.
  • VBA_CODE: Tên file định dạng .BAS, chứa nội dung VBA.
  • VBA_MODULE: Tên module trong file .BAS

Related Docs

Overview
Data Step
Data Aggregation
Data Visualization
Proc SQL
Variable Analysis
Macro
Model Regression
Variable Selection
Model Assessment