此原文作者Ted Conway, Chicago, IL介紹利用SAS中的MACRO語法配合Visual Basic語法來實現大量資料檔其描述性統計特性:長條圖(Bar)與線(Line)圖
此法概念來自於原出處如下圖
步驟如下
1.先建立SAS MACRO語法定義EXCEL圖檔
2.利用SAS執行時將建立的MACRO語法轉換為EXCEL的VBA語言
3.再將VBA語言表現在EXCEL資料表上並繪製出圖檔
1.先將以下MARCO匯入SAS並執行
作者提供的MACRO語法
%macro chart(type, name=, charttype=bar, across=1, cell=a1, data=, catvar=, xvar=, yvar=, yformat=, ymin=0, ymax=, yint=);
%if &type=workbook %then %do;
%global gblworkbook; %let gblworkbook=&name;
data _null_;
file "c:\temp\chart.vbs" mod; * Note: .vbs file should not exist at start of processing;
put 'Set XL = CreateObject("Excel.Application")' / 'XL.Visible=True' /
'XL.Workbooks.add' / "XL.ActiveWorkbook.SaveAs ""&gblworkbook"", -4143";
%end;
%if &type=worksheet %then %do;
data _null_; file "c:\temp\chart.vbs" mod; put "XL.Sheets.Add.name = ""&name"""; %global gblworksheet; %let gblworksheet=&name;
%end;
%if &type=chart %then %do;
proc sql noprint;
select distinct &catvar into :categories separated by '|' from &data order by &catvar;
%let catnum=1;
%let cat=%scan(&categories,&catnum,'|');
%do %while("&cat"^="");
%let lrow=%eval(%sysfunc(floor(%eval(&catnum-1)/&across))+1);
%let lcol=%sysfunc(mod(&catnum,&across));
%if &lcol=0 %then %let lcol=&across;
data _null_;
set _last_(where=(&catvar="&cat")) end=eof;
length xvalues yvalues $ 32000;
retain xvalues yvalues '';
xvalues=trim(xvalues)||'""'||trim(&xvar)||'"",';
yvalues=trim(yvalues)||trim(&yvar)||',';
if eof;
file "c:\temp\chart.vbs" mod;
put "XL.windows(""%scan(&gblworkbook,-1,'\')"").Activate" / 'XL.Range("A1").Select' / "XL.Charts.Add";
%if &charttype=bar %then put "XL.activechart.ChartType = 51";; %if &charttype=line %then put "XL.activechart.ChartType = 4";;
put "XL.activechart.SeriesCollection.NewSeries" / "XL.activechart.SeriesCollection(1).XValues = ""={" xvalues +(-2) '}"' /;
put 'XL.activechart.SeriesCollection(1).Values = "={' yvalues +(-2) '}"' / 'XL.activechart.SeriesCollection(1).Name = "=""' "&cat" '"""' /
'XL.activechart.Location 2, "' "&gblworksheet" '"'; *2=xlLocationAsObject;
put 'XL.activechart.Axes(1).MajorTickMark = -4142' / 'XL.activechart.Axes(1).MinorTickMark = -4142' / 'XL.activechart.Axes(2).MajorTickMark = -4142' /
'XL.activechart.Axes(2).MinorTickMark = -4142' / "XL.activechart.Axes(2).MinimumScale = &ymin" / "XL.activechart.Axes(2).MaximumScale = &ymax" /
"XL.activechart.Axes(2).MinorUnit = &yint" / "XL.activechart.Axes(2).MajorUnit = &yint" / 'XL.activechart.Axes(2).TickLabels.NumberFormat =' ""&yformat"";
put 'x = XL.worksheets(' """&gblworksheet""" ').ChartObjects(XL.worksheets(' """&gblworksheet""" ').ChartObjects.Count).Left';
%if &lcol=1 %then
put 'XL.worksheets(' """&gblworksheet""" ').ChartObjects(XL.worksheets(' """&gblworksheet""" ').ChartObjects.Count).Left = XL.worksheets(' """&gblworksheet"""
').Columns(' "XL.range(""&cell"").column" ').Left'%str(;);
%else %do;
put 'XL.worksheets(' """&gblworksheet""" ').ChartObjects(XL.worksheets(' """&gblworksheet""" ').ChartObjects.Count).left = _';
put 'XL.worksheets(' """&gblworksheet""" ').Columns(' "XL.range(""&cell"").column" ').Left + _';
put "(&lcol-1) * " 'XL.worksheets(' """&gblworksheet""" ').ChartObjects(XL.worksheets(' """&gblworksheet""" ').ChartObjects.Count).width';
%end;
put 'XL.activechart.Legend.Delete';
%if &lrow=1 %then
put 'XL.worksheets(' """&gblworksheet""" ').ChartObjects(XL.worksheets(' """&gblworksheet""" ').ChartObjects.Count).Top = XL.worksheets(' """&gblworksheet"""
').Rows(' "XL.range(""&cell"").row" ').Top'%str(;);
%else %do;
put 'XL.worksheets(' """&gblworksheet""" ').ChartObjects(XL.worksheets(' """&gblworksheet""" ').ChartObjects.Count).Top = _';
put 'XL.worksheets(' """&gblworksheet""" ').Rows(' "XL.range(""&cell"").row" ').Top + _';
put "(&lrow-1) * " 'XL.worksheets(' """&gblworksheet""" ').ChartObjects(XL.worksheets(' """&gblworksheet""" ').ChartObjects.Count).height';
%end;
%let catnum=%eval(&catnum+1);
%let cat=%scan(&categories,&catnum,'|');
%end;
%end;
%if &type=create %then %do;
data _null_; file "c:\temp\chart.vbs" mod; put "XL.ActiveWorkbook.Save"; run; x 'c:\temp\chart.vbs';
%end;
%mend;
2.將SAS MACRO語法執行並將起始語法設定好
依照原作者,在MACRO語法中共分為四個步驟皆是以%chart();開頭
| 1.%chart(workbook, name=c:\temp\chart.xls); 2.%chart(worksheet, name=TestBar); 3.%chart(chart, charttype=bar,cell=a1, across=1, data=work.king , catvar=catvar , xvar=xvar , yvar=yvar, yFormat="#,##0" , ymax=10000, yint=2000 ); 4.%chart(create); |
Step 1.%chart(workbook, name=c:\temp\chart.xls);
需先存在workbook關鍵字,並在name=後面打上excel未來的存放位置,預設值為c:\temp\chart.xls,若遇到繪出位置無該資料夾則須先自行建立該資料夾,如c:內無temp資料夾,則自行先建立temp資料夾方可使用
Step 2.%chart(worksheet, name=TestBar);
須先打上worksheet,為excel檔案內的分頁,並將命名為name=TestBar,也可自行命名其他分頁名稱
Step 3.%chart(chart, charttype=bar,cell=a1, across=1, data=work.king , catvar=catvar , xvar=xvar , yvar=yvar, yFormat="#,##0" , ymax=10000, yint=2000);
須先打上chart
charttype=bar,為輸出在EXCEL檔時圖檔為bar,也可打line
cell=a1,為圖檔的起始位置,此為EXCEL檔內a1 cell的位置
across=1,為圖檔並排的數目,若圖檔預計有10張,若across=2則左右會有兩張並排而垂直會有5張
data=,為SAS程式內欲輸出的檔案
catvar=,為SAS檔案欲輸出的分層變項(須為文字型態),內有十層即可繪製出10張圖檔
xvar=,為SAS檔案中欲繪製在EXCEL圖檔中x軸的變項(須為文字型態)
yvar=,為SAS檔案中欲繪製在EXCEL圖檔中y軸的變項(須為數字型態)
yFormat="#,##0",EXCEL圖檔中y軸座標格式,可加也可不加
ymax=10000,顧名思義為y軸資料的最大值
yint=2000,為y軸資料個間距
Step 4.%chart(create);
打上create做為結尾的部份,打上此段語法後SAS執行完就會將資料繪製到EXCLE檔圖檔中
附註:Step 3. 可重複出現例如同時繪製chart與line圖 可以在語法中打2組Step 3.的語法
實測如下
data king; input catvar$ xvar$ yvar; cards; 1 5000 200 1 4000 300 1 2000 400 1 3000 200 1 1000 200 1 3000 200 2 1000 300 2 2000 500 2 3000 100 2 2000 200 2 5000 200 3 1000 100 3 3000 200 3 5000 600 3 2000 1000 3 10000 1500 ; run; %chart(workbook, name=c:\temp\chart.xls); %chart(worksheet, name=king); %chart(chart, charttype=bar,cell=a1, across=1, data=work.king , catvar=catvar , xvar=xvar , yvar=yvar, yFormat="#,##0" , ymax=10000, yint=2000 ); %chart(create); |
完成後如下圖(下圖為EXCEL 2007 表示也能用)
作者提供以下範例可直接複製並執行
proc sql; * Create some sample data;
create table prod1993 as
select product, month as monum, put(month,monname3.) as month, sum(actual) as actual
from sashelp.prdsale(where=(year=1993)) group by 1, 2, 3 order by 1, 2;
%chart(workbook, name=c:\temp\chart.xls);
%chart(worksheet, name=TestBar); * Example #1 – Three-Across Bar Charts;
%chart(chart, charttype=bar, cell=b2, across=3, data=prod1993, catvar=product, xvar=month,
yvar=actual, yFormat="#,##0", ymax=10000, yint=2000);
%chart(worksheet, name=TestLine); * Example #2 – One-Across Line Charts;
%chart(chart, charttype=line, cell=a1, across=1, data=prod1993, catvar=product, xvar=month,
yvar=actual, yFormat="#,##0", ymax=10000, yint=2000);
%chart(worksheet, name=TestBoth); * Example #3 – Three-Across Bar and Line Charts;
%chart(chart, charttype=bar, cell=a1, across=3, data=prod1993, catvar=product, xvar=month,
yvar=actual, yFormat="#,##0", ymax=10000, yint=2000);
%chart(chart, charttype=line, cell=a40, across=3, data=prod1993, catvar=product, xvar=month,
yvar=actual, yFormat="#,##0", ymax=10000, yint=2000);
%chart(create);
3.再將VBA語言表現在EXCEL資料表上並繪製出圖檔
以下為MACRO執行後在EXCEL內實現的VBA程式碼,僅供參考
Set XL = CreateObject("Excel.Application")
XL.Visible=True
XL.Workbooks.add
XL.ActiveWorkbook.SaveAs "c:\temp\chart.xls", -4143
XL.Sheets.Add.name = "TestBar"
XL.ActiveSheet.PageSetup.CenterHorizontally = True
XL.ActiveSheet.PageSetup.CenterVertically = True
XL.ActiveSheet.PageSetup.Orientation = 2
XL.ActiveSheet.PageSetup.FitToPagesWide = 1
XL.ActiveSheet.PageSetup.FitToPagesTall = 1
XL.ActiveSheet.PageSetup.Zoom = False
XL.windows("chart.xls").Activate
XL.Range("A1").Select
XL.Charts.Add
XL.activechart.ChartType = 51
XL.activechart.SeriesCollection.NewSeries
XL.activechart.SeriesCollection(1).XValues = "={""Jan"",""Feb"",""Mar"",""Apr"",""May"",""Jun"",""Jul"",""Aug"",""Sep"",""Oct"",""Nov"",""Dec""}"
XL.activechart.SeriesCollection(1).Values = "={4085, 5025, 4918, 6999, 5727, 7615, 8189, 5754, 4038, 5284, 4890, 6939}"
XL.activechart.SeriesCollection(1).Name = "=""BED"""
XL.activechart.Location 2, "TestBar"
XL.activechart.Axes(1).MajorTickMark = -4142
XL.activechart.Axes(1).MinorTickMark = -4142
XL.activechart.Axes(2).MajorTickMark = -4142
XL.activechart.Axes(2).MinorTickMark = -4142
XL.activechart.Axes(2).MinimumScale = 0
XL.activechart.Axes(2).MaximumScale = 10000
XL.activechart.Axes(2).MinorUnit = 2000
XL.activechart.Axes(2).MajorUnit = 2000
XL.activechart.Axes(2).TickLabels.NumberFormat ="#,##0"
x = XL.worksheets("TestBar").ChartObjects(XL.worksheets("TestBar").ChartObjects.Count).Left
XL.worksheets("TestBar").ChartObjects(XL.worksheets("TestBar").ChartObjects.Count).Left =
XL.worksheets("TestBar").Columns(XL.range("b2").column).Left
XL.activechart.Legend.Delete
XL.worksheets("TestBar").ChartObjects(XL.worksheets("TestBar").ChartObjects.Count).Top =
XL.worksheets("TestBar").Rows(XL.range("b2").row).Top
沒有留言:
張貼留言