SAS Macro学习记录

/*****************************************************************************

@Purpose: 该SAS宏程序用于定义ods输出excel的格式；

@Version: 1.0

@Author: ming.jiang

@Date: 14Mar2023

@MacroName:Ods2XLS

@Macro Parameter如下：

inds：输入数据集的名称

title1: 输出的excel标题的名称 e.g, ReportID+Report Title

title2: 输出的excel导出时间 e.g, 系统时间 Run on: yyyy年mm月dd日hh小时MM分钟ss秒

title3：输出的excel中的report description

sheetname:输出的 excel file的sheet name

*******************************************************************************/

%Macro Ods2XLS(inds=%str(),title1=%str(),title2=%str(),title3=%str(),sheetname=%str());

dm "log; clear; output; clear; odsresults; clear;";

/*******检查输入的数据集文件是否存在***********/

%if not %sysfunc(fileexist(&inds.)) %then %do;

%put &inds.;

%put %sysfunc(exist(&inds.));

%put NOTE: please check your dataset name;

%goto exit;

%end;

%local data dsname;

%if %index(&inds.,.sas7bdat) gt 0 %then %do;

%let dsname= %scan(&inds.,-1,"\");

libname lib "%sysfunc(substr(&inds.,1,%index(&inds.,&dsname.)-1))"; /*substr函数从第一位开始截取字符串，与其他编程语言不一样*/

%let data=%sysfunc(tranwrd(&dsname.,.sas7bdat,)); /*tranwrd函数第二个参数不带引号*/

/*

%put %index(&inds.,.sas7bdat); %宏函数%index的第二个参数不能带单双引号

%put %sysfunc(substr(&inds.,1,%index(&inds.,.sas7bdat)-1));

%put dsname=&dsname.;

%put data=&data.;

*/

%put "%sysfunc(substr(&inds.,1,%index(&inds.,&dsname.)-1))";

%end;

%put NOTE: lib.&data. ;

/********将input dataset的变量放入到macro Var中*********/

proc sql noprint;

select count(*) into:RowsCount from lib.&data.; /*获取input dataset的行数赋值给宏变量 RowsCount*/

select count(*) into:ColumnsCount from dictionary.columns where libname=%upcase('lib') and memname= %upcase("&data."); /*获取input dataset的列数赋值给宏变量 ColumnsCount*/

select distinct name into :Col1- :Col%left(&ColumnsCount.) from dictionary.columns where libname=%upcase('lib') and memname=%upcase("&data."); /***将dataset 中的列名逐一赋值到宏变量col1中 **/

select type into :Type1- :Type%left(&ColumnsCount.) from dictionary.columns where libname=%upcase('lib') and memname=%upcase("&data."); /*将dataset中的列的变量类型逐一赋值到宏变量type1中**/

quit;

%put Note: RowsCount= &RowsCount.;

%put Note: ColumnsCount= &ColumnsCount.;

/*%put %sysfunc(Lengthc(&ColumnsCount.));*/

/*%put LeftColumnsCount=%left(&ColumnsCount.);*/

%local OutputFileName;

%if %index(&inds.,.sas7bdat) gt 0 %then %do;

%let OutputFileName=%sysfunc(tranwrd(&inds.,.sas7bdat,.xlsx));

%end;

%else %do;

%let OutputFileName= &inds.;

%end;

%put Note OutputFileName=&OutputFileName.;

%local _title1 _title2 _title3 _sheetname;

%if %length(&title1.)=0 %then %do;

%let _title1="Report ID+Report Title: None";

%end;

%else %do;

%let _title1=&title1.;

%end;

%if %length(&title2.)=0 %then %do;

%let _title3="Report Description: None";

%end;

%else %do;

%let _title3="报告描述："&title2.;

%end;

%let _title2=报告运行时间：%sysfunc(today(),IS8601DT.);

%if %length(&sheetname.)=0 %then %do;

%let _sheetname="&data.";

%end;

%else %do;

%let _sheetname=&sheetname.;

%end;

%put &_title1.;

%put &_title2.;

%put &_title3.;

%put &_sheetname.;

ods excel file="&OutputFileName."

options(sheet_name=&_sheetname. embedded_titles="yes" frozen_headers="yes"); /*option属于 ods excel中的选项，不用分号分割语句*/

title1 justify=center &_title1.;

title2 justify=right &_title2.;

title3 justify=left &_title3.;

proc report data=lib.&data.;

column _all_;

/*label style="my style";*/

/*label sqfeet="my testing";*/

run;

ods excel close;

%exit: %put Note:The input dataset does not exist, please check;

%Mend;

%Ods2XLS(inds=C:\Users\bioray\OneDrive\sas\proc sql\SQLDatasetsV9\houses2.sas7bdat,title1="PK001_T PK采血时间窗核查",title2="核查PK采血时间窗",sheetname="PK001_T"); /*输入的宏参数不带引号*/