SAS-100种输出Excel的方法，你在用哪种？
SAS-100种输出Excel的方法，你在用哪种？
你是被标题吸引来的么？那么小编就要向你道歉了，100种？当然是没有的，但还是有很多中，小编今天的要盘点的一下SAS输出Excel的方法，各种方式之间的优劣。其实好几天前就打算写的，也是在好几天前开始酝酿，一直没有空下来的时间，年底了，各种忙，小编的时间就像是被挤干了的海绵一样，这几天怎么挤都挤不出。废话好像有点多，还是回到今天的主题上~
今天主要要分享的是Excel的导出，初步计划分为3块，最简单proc export,经典的ods tagset.excelxp,以及另类的ods excel,至于好玩的DDE（比较老的用法）请见小编历史推送文章。奥，对了，鼠标点的输出方法请自行百度。小编和鲁迅一样有俩颗树，小编的一颗树是百度，另一颗树还是百度。好吧，技术并不好的小编就不再给大家强行说教了。
1 Proc export
proc export是最长见最简单输出方式。为啥简单呢，因为就是简单，会点SAS的人都会吧。不过小编今天还是想说一说这个，请不要嫌烦，因为你可能会有意想不到的收获哦~
01 proc export的结构
proc exportdata= contents
outfile="&path.\&xlsname..xlsx"
dbms=xlsx replacelabel;
sheet="contents";
newfile= yes;
run;结构就张这个样，没啥好特别要说的，有一点要说的如果你SAS安装的有问题，或者电脑的office安装的有问题，dbms这个引擎还是值得考究一下的。下面是小编针对proc export进行的一个批量的输出，这么简单的程序，只能叫循环不能叫Macro，实现的效果是将逻辑库下的数据集全部输出。在第一个sheet会生成一个Contents的目录，可以用超链接快速跳转到指定sheet，唯一有遗憾的是，超链接的单元格要点击一下，函数才能生效，针对这个问题，小编研究了一下午也没找到解决的办法，如诸位大神知道，请不吝留言告知，或加小编微信告知，万谢！
%macro doloop(path=G:\微信公众号\MacroFolder\excel,xlsname=Test,lib=EXCEL);
proc contentsdata=Excel._ALL_ out=_varstemp10(keep=MEMNAME) DIRECTORY NOPRINT MEMTYPE=dataCENTILES;
proc sortdata=_varstemp10 out=_varstemp10 nodupkey;
by MEMNAME;
run;
data contents;
set_varstemp10;
N=_N_;
sht=compress('=HYPERLINK("#'||MEMNAME||'!R1C1","'||MEMNAME||'")');
Label N="Sheet SEQ" MEMNAME="DataSetName" ;
run;
proc sqlnoprint;
selectcount(distinct memname) into: nn from dictionary.columns where libname=%upcase("&lib.");
selectdistinct memname into:mem1-:mem%left(&nn.) from dictionary.columns where libname=%upcase("&lib.");
quit;
proc exportdata= contents
outfile="&path.\&xlsname..xlsx"
dbms=xlsxreplace label;
sheet="contents";
newfile= yes;
run;
%do i=1%to &nn;
proc exportdata= &lib..&&mem&i.
outfile="&path.\&xlsname..xlsx"
dbms=xlsxreplace label;
sheet="&&mem&i.";
newfile= no;
run;
%end;
%mend;
%doloop;实现的效果：
proc export就这么多了，吃正菜前的小甜点，还是请大家耐心看下去，今天的有点长，重点在后面。小编将详细的讲解ods tagsets.excelxp输出方式，不管你是大神还在小白，还是请看下去，兴许小编能给你一点别的启示，好吧，我这么强烈的自卖自夸好像真不太好，也好像明白了一个朋友说我“絮絮叨叨”的~好像真是哎
2 ods tagsets.excelxp
ods tagsets.excelxp是一个比较经典的输出方式，proc export输出是有限制的，如果数据集带有format是输出不了，只能输出真实值，而通过ods进行输出是一个很好的方式，因为可以用很多过程步来实现输出或者在计算。以及Excel各种样式的设计。在讲用法前，我还是要从安装将起来，ods tagsets官网是有更新的，或者说完善其功能，现在大家用的SAS可能是几年前出的，甚至是SAS9.4安装的tagsets也不见得一定很完美。
01 安装等介绍
一个文件：exclags.tpl,大家可以自行百度，也可去小编百度网盘下载，建议还是去小编网盘下载，小编自己对此文件进行了编辑，编辑后的文件比从官网下载的友好一点。（这个文件能解决啥问题呢：比如说吧，如果你tagsets输出的Excel里面会随机出现空格等问题，更新此文件后就可以完美解决），此文件如何更新呢，下载后，请把它当做SAS程序，直接运行就好，运行后，我强烈建议如果有兴趣做深入研究朋友，还是好好看这个问题，知识点好多哎，option什么的参数啊怎么填啊，真的很全很详细，比官网的help好出很多，可以说网上难以找到第二个与之媲美的文档。如果你觉得有，一定要告诉小编，小编要收藏！！！
为啥值得看呢 ：我来截图一部分：（截图仅仅一部分，下面还有很多参数说明及参数填值的说明：这些option都是针对于ods tagsets的）
本文由“壹伴编辑器”提供技术支持
02 tagsets程序写法介绍
写法其实没有太多好说的：先以简单的写法来说：
odspathtpt.template(read) sasuser.templat(read) sashelp.tmplmst(read);
odslistingclose;
ods RESULTS off;
ods tagsets.excelxp file="D:\日常练习\OdsExcel\tagsets\Listing News .xls" options(contents='yes' FROZEN_HEADERS='yes' )style=tag_1 ;
odstagsets.ExcelXPoptions(embedded_titles='no'embedded_footnotes='no'FORMULAS='on');%let ps1=800;%letls1=256;
Optionsps=&ps1ls=&ls1 formchar=" ___________"nodatenonumbernocenter;
optionsnotes;
%tag_rep_ds(inds=sashelp.class,title=%str(Test5),
sheetname=%str(Test7),contents=%str(Test6))
%tag_rep_ds(inds=sashelp.class,title=%str(Test9),
sheetname=%str(Test9),contents=%str(Test9))
ods tagsets.excelxp close;
ods listing;如上，是一个大体结构：显示 ods 这是一些，接着就是 一个Macro（report的Macro实现数据的输出，控制数据的sheet,contents，title）然后在就ods结束输出。
这个时候我该说几个option选项了：
contents=‘yes’ 这个是时候回在第一个sheet里面生成一个contents目录，可以实现点击后跳转的指定sheet。（tagset输出的contents文件路径移动了就实现不了跳转，我显示跳转的方法后面Macro里面讲到的。）
FROZEN_HEADERS=‘yes’ 实现Excelheader冻结。
还有很多option参数大家可以去前面说的那个文件里面查看用法，我就不做过多叙述了。
来看一下输出结果：
当然说到ods就必须要说template(style)的设计（可见本公众号历史文章：RTF精美排版背后的Code里面提到的template的用法和资料）。我这里就直接粘贴上本次的proc template代码(由于排版不美观删除部分代码，无关紧要的代码)。
%macro tag_template;
optionsnofmterr missing=" ";
proc template;
define styleStyles.tag_1;
parent =styles.printer;
style fonts/
"TitleFont2" = ("Times New Roman",8pt, Medium)
"TitleFont" = ("Times New Roman",8pt, Medium)
"StrongFont" = ("Times New Roman",8pt,Medium)
"FixedStrongFont" = ("Times New Roman",8pt,Medium)
"FixedHeadingFont" = ("Times New Roman",8pt,Medium)
"headingFont" = ("Times New Roman",8pt,Medium)
"docFont" = ("Times New Roman",8pt,Medium);
*定义表头;
style header /
backgroundcolor=#B2DFEE
color=black
font=("Times New Roman",10pt)
fontweight=bold;
*定义文件的布局;
style bodyfrom document /
bottommargin =0mm
topmargin = 0mm
rightmargin = 0mm
leftmargin = 0 mm;
STYLE Table /
RightMargin=0 cm
LeftMargin=0 cm
TopMargin= 0 cm
BottomMargin=0 cm
CELLSPACING = 0
CELLPADDING = 0
FRAME = void
RULES = All
OUTPUTWIDTH = 925;
end;
run;
%mend tag_template;
%tag_template;上面的template就结束了，这个时候就要看ods见的proc report那个Macro怎么写的呢。代码有点多，Macro里面有注释，大家可以看，如果不太懂可以看本公众号历史文章，有很多proc report的写法。学好数理化走边天下都不怕，在SAS里面学好report等于学好数理化中的一门了吧~
%macro tag_rep_ds(inds=%str(),title=%str(),sheetname=%str(),contents=%str());
optionsnofmterr compress=yes missing='';
/*-------- 检查输入的数据集是否正确-------------------*/
%local data lib;
%if &inds.= %then%do;
%put NOTE:plesce check your dataset name;
%goto exit;
%end;
%if%length(%sysfunc(compress("&inds.",".")))ne %length(%sysfunc(compress("&inds.",""))) %then%do;
%let lib=%upcase(%scan("&inds.",1,"."));
%let data=%upcase(%scan("&inds.",2,"."));
%end;
%else%do;
%let lib=WORK;
%let data=%upcase(&inds.);
%end;
/*-------- 建变量放入Macro Var 中-------------------*/
proc sqlnoprint;
selectcount(*) into: nvars from &lib..&data;
selectcount(*) into: ss from dictionary.columns where libname="&lib" and memname="&data";
selectdistinct name into: mm1-:mm%left(&ss) from dictionary.columnswhere libname="&lib" and memname="&data";
select typeinto: nn1-:nn%left(&ss) from dictionary.columns wherelibname="&lib" and memname="&data";
quit;
/*-------- 判断数据集是否有观测，如果有观测执行下面代码-------------------*/
%if &nvars>0%then%do;
odstagsets.excelxp options(sheet_name="&sheetname" );
proc reportdata=&lib..&data headskip headline nowd contents="&contents"
/*-------- 设置header格式样式（边框）-------------------*/
style(header)={just=casis=on font_weight=bold nobreakspace=off
bordertopcolor=blackborderbottomcolor=black BORDERRIGHTCOLOR=black
bordertopwidth=.5 borderbottomwidth=.5 BORDERRIGHTwidth=.5 } ;
column ("&title" _ALL_);
%do t=1%to &ss;
/*-------- 设置column格式样式（边框）-------------------*/
define&&mm&t / display style=[ just=left tagattr='text'just=c asis=on
bordertopcolor=blackborderbottomcolor=black BORDERRIGHTCOLOR=black
borderbottomwidth=.5 BORDERRIGHTwidth=.5bordertopcolor=black bordertopwidth=.5 nobreakspace=off ];
%end;
run;
%end;
/*-------- 判断数据集是否有观测如果没有观测执行下面代码-------------------*/
%else%do;
/*-------- 创建一个变量和衍生生产一个新的数据集-------------------*/
data&data._m;
set &lib..&data;
_tem102_s=.;
run;
/*-------- 插入一个值，使之非数据集非空-------------------*/
proc sql;
insert into&data._m(_tem102_s)
values (2 )
;
quit;
odstagsets.excelxp options(sheet_name="&sheetname" );
proc reportdata=&data._m headskip headlinenowd contents="&contents"
/*-------- 设置header格式样式（边框）-------------------*/
style(header)={just=casis=on bordertopcolor=black borderbottomcolor=black BORDERRIGHTCOLOR=black
borderbottomwidth=.5 BORDERRIGHTwidth=.5bordertopcolor=black bordertopwidth=.5 font_weight=boldnobreakspace=off} ;
column ("&title" _ALL_);
%do t=1%to &ss;
/*-------- 设置column格式样式（边框）-------------------*/
define&&mm&t / display style(column)=[ just=left tagattr='text'
asis=onbordertopcolor=white borderbottomcolor=white
BORDERRIGHTCOLOR=white
borderbottomwidth=0 BORDERRIGHTwidth=0bordertopcolor=white bordertopwidth=0 font_weight=Medium background=White] ;
%end;
define_tem102_s / display style(column)=[ just=left tagattr='text'] ;
define_tem102_s / noprint ;
/*-------- 插入一段空白话-------------------*/
computebefore / style = [just=cfont_weight=medium font_size=8pt borderbottomcolor=blackborderbottomwidth=.5pt background=white];
line "There are no observations toreport. An empty listing will be created.";
endcomp;
run;
proc deletedata=&data._m;
quit;
%end;
%exit:
%mend;%tag_rep_ds：我将其定义为辅助性Macro，并非功能性Macro。因为它不能单独实现一个输出文件。需要配合起来。上面的代码和Macro组合起来就可以实现一个ods tagsets的基本结构。然而我并不满足于这个基本结构实现的输出，我想要自定义Contents，自定义索引等等，是不是觉得小编有些作。小编觉得吧，既然要做那就要力所能及的做好，毕竟没做一件事都打上了自己的logo。要对的起自己，正如我们这个年龄，就应该“以梦为马，莫负韶华”。
接下来小编将讲本文的重头戏了，也是小编成就感爆棚的东东，看起来很幼稚哎。Macro如下，注释等横详细了，时间有点晚了，看来今天是将不玩了ods excel 小编打算放到明天再讲ods excel.
%macro tag_sas2xls(path=%str(),xlsname=%str(),dslist=%str(),FROZEN=%str(yes),contents=%str(NO),style=tag_1);
/*-------- 处理dslist、拆分ds、Sheetname、Contents、title-------------------*/
*dslist格式要求：ds\sheetname\contents\title|ds\sheetname\contents\title|.....|ds\sheetname\contents\title
;
/*针对如下情况处理：dsnsht cnt tle*/
%let i=1;
%do%while(%qscan(&dslist,&i,|)^=%str());
%let list&i=%qscan(&dslist,&i,|);
%let dsn&i=%qscan(&&list&i.,1,\);
%let sht&i=%qscan(&&list&i.,2,\);
%let cnt&i=%qscan(&&list&i.,3,\);
%let tle&i=%qscan(&&list&i.,4,\);
%if%length(&&sht&i)=0 %then%do;
%let sht&i=&&dsn&i;
%end;
%if%length(&&cnt&i)=0 %then%do;
%let cnt&i=&&sht&i;
%end;
%if%length(&&tle&i)=0 %then%do;
%let tle&i=&&cnt&i;
%end;
%put NOTE: &&dsn&i&&sht&i &&cnt&i &&tle&i;
%let i=%eval(&i+1);
%end;
%let _loop=%eval(&i-1);
datacontents_INDX;
lengthcontents $20000.;
contents="&dslist.";
run;
/*类型2的输出*/
data contents_INDX;
retain n;
setcontents_INDX;
length dsn shtcnt tle $400.;
n=count(contents,'|');
j=n+1;
do i=1 to j;
list=compress(scan("&dslist",i,'|'),'');
dsn=compress(scan(list,1,'\'),'');
sht=compress(scan(list,2,'\'),'');
cnt=compress(scan(list,3,'\'),'');
tle=compress(scan(list,4,'\'),'');
n=i;
ifmissing(sht) then sht=dsn;
ifmissing(cnt) then cnt=sht;
ifmissing(tle) then tle=cnt;
sht=compress('=HYPERLINK("#'||sht||'!R1C1","'||sht||'")');
Label N="Sheet SEQ" dsn="DataSet Name" sht="Sheet Name(Please click down)" tle="Listing Title/Description" ;
outputcontents_INDX;
end;
keep dsn sht tle n;
run;
ods pathtpt.template(read) sasuser.templat(read)sashelp.tmplmst(read);
ods listingclose;
ods RESULTSoff;
odstagsets.excelxp file="&path.\&xlsname..xls" options(contents="&contents." FROZEN_HEADERS="&FROZEN." ) style=&style. ;
odstagsets.ExcelXP options(embedded_titles='no'embedded_footnotes='no');
%let ps1=800;
%let ls1=256;
Optionsps=&ps1 ls=&ls1 formchar="___________" nodatenonumber nocenter;
options notes;
odstagsets.excelxp options(sheet_name="Contents_Index" absolute_column_width='yes') ;
proc reportdata=contents_INDX headskip headlinenowd
/*-------- 设置header格式样式（边框）-------------------*/
style(header)={just=casis=on font_weight=bold nobreakspace=off
bordertopcolor=blackborderbottomcolor=black BORDERRIGHTCOLOR=black BORDERLEFTCOLOR=black
bordertopwidth=.5 borderbottomwidth=.5 BORDERRIGHTwidth=.5 BORDERLEFTwidth=.5 } ;
column ("Contents of Table" _ALL_);
/*-------- 设置column格式样式（边框）-------------------*/
define N/display style=[ just=left tagattr='text' just=c asis=on
bordertopcolor=blackborderbottomcolor=black BORDERRIGHTCOLOR=black
borderbottomwidth=.5 BORDERRIGHTwidth=.5bordertopcolor=black bordertopwidth=.5 nobreakspace=off ] ;
define dsn/display style=[ just=left tagattr='text' just=c asis=on
bordertopcolor=blackborderbottomcolor=black BORDERRIGHTCOLOR=black
borderbottomwidth=.5 BORDERRIGHTwidth=.5bordertopcolor=black bordertopwidth=.5 nobreakspace=off ];
define sht/display style=[ just=left tagattr='text' just=c asis=on
bordertopcolor=blackborderbottomcolor=black BORDERRIGHTCOLOR=black
borderbottomwidth=.5 BORDERRIGHTwidth=.5bordertopcolor=black bordertopwidth=.5 nobreakspace=off ] ;
define tle/display style=[ just=left tagattr='text' just=c asis=on
bordertopcolor=blackborderbottomcolor=black BORDERRIGHTCOLOR=black
borderbottomwidth=.5 BORDERRIGHTwidth=.5bordertopcolor=black bordertopwidth=.5 nobreakspace=off ];
run;
odstagsets.excelxp options( absolute_column_width='yes') ;
%do mlop=1%to &_loop.;
%tag_rep_ds(inds=%str(&&dsn&mlop),title=%str(&&tle&mlop),sheetname=%str(&&sht&mlop),contents=%str(&&cnt&mlop));
%end;
odstagsets.excelxp close;
ods listing;
%mend;%tag_sas2xls(path=%str(D:\Ods Excel\tagsets),xlsname=%str(Test),dslist=%str(SAShelp.class\sheetname\contents|class1\test1\超链接));效果：（点击C列可以实现跳转）
好吧今天很晚了，小编要睡觉了~ods excel以后再说，关于那个我还有点没研究透彻~