日常項(xiàng)目中，程序或Outputs一般會(huì)提前確定好剿涮，通過(guò)Excel Tracker或者文件管理系統(tǒng)進(jìn)行管理言津。各個(gè)公司一般也有相關(guān)工具進(jìn)行檢查，以確保Tracker中的程序或Outputs都在輸出文件夾中取试。
不過(guò)悬槽，本公司工具不會(huì)檢查文件夾中多余的程序或Outputs。于是瞬浓，寫(xiě)了個(gè)SAS宏程序用于實(shí)現(xiàn)這個(gè)功能初婆。這個(gè)宏程序也可用于檢查其它所需文件，例如xml。
宏程序匯總在文章末尾磅叛，若參考引用屑咳，需要結(jié)合各自項(xiàng)目路徑結(jié)構(gòu)，以及Tracker信息弊琴，來(lái)更新程序兆龙。
宏程序的分為3個(gè)部分：
- 獲取系統(tǒng)或Tracker中的文件名稱(chēng)
- 獲取文件夾中已有的文件名稱(chēng)
- 通過(guò)比較以上兩類(lèi)名稱(chēng)，獲取缺失或多余的文件
第一部分敲董，可能通過(guò)公司的管理工具獲取紫皇，也可以簡(jiǎn)單導(dǎo)入EXCEL Tracker。
options validvarname = v7;
proc import datafile = "/xxx/xxx/xxx.xlsx"
out = tfls dbms = xlsx replace;
run;
第二部分腋寨，可以通過(guò)Dopen系列函數(shù)獲取文件名稱(chēng)聪铺，具體可以參考文章SAS編程：如何獲取某路徑下文件的名稱(chēng)？萄窜，完整內(nèi)容可以整合成一個(gè)宏程序铃剔。
%macro get_file(dirpath =, outdt =, type = sas );
%if "&dirpath." ne "" %then %do;
%local dirpath_tmp slash;
%let slash = %substr(%sysfunc(compress(&dirpath., : _ , a d)), 1, 1);
*Remove trailing slash;
%if "%substr(&dirpath., %length(&dirpath.),1)"= "&slash." %then %let dirpath_tmp=%substr(&dirpath.,1, %length(&dirpath.) -1);
%else %let dirpath_tmp = &dirpath.;
** Dopen--Get filepath;
data &outdt.;
fileres = filename("dirpath", "&dirpath_tmp");
dirid = dopen("dirpath");
num = dnum(dirid);
length direct filename filepath $200;
if dirid >0 and num >0 then do;
do i=1 to num;
direct = "&dirpath_tmp.";
filename = dread(dirid, i);
filepath = catx("&slash.", direct, filename);
%if %index(%upcase(&type), SAS) %then %do;
if strip(scan(filename, 2, "."))="sas" then output;
%end;
%if %index(%upcase(&type), RTF) %then %do;
if strip(scan(filename, 2, "."))="rtf" then output;
%end;
%if %index(%upcase(&type), XLSX) %then %do;
if strip(scan(filename, 2, "."))= "xlsx" then output;
%end;
end;
end;
keep filename filepath;
proc sort;
by filename;
run;
%end;
%mend get_file;
%get_file(
dirpath = &path_ana./tables
,outdt = source_pgm_tables
,type = sas
);
第三部分，可以通過(guò)SQL簡(jiǎn)單的篩選呈現(xiàn)缺失或多余的文件脂倦。
%let out_type = tables;
%let type = T;
proc sql noprint;
**in tracker not in folder;
create table source_in_tracker_&out_type. as
select output_number, base_output_name, program_name, programmer, tester
from tfls
where output_type = "&type." and program_name not in
(
select filename from source_pgm_&out_type.
)
;
**in folder not in tracker;
create table source_in_folder_&out_type. as
select *
from source_pgm_&out_type.
where filename not in
(
select program_name from tfls
)
;
quit;
以上三部分番宁，可以整合成一個(gè)完整的宏程序，用于檢查同一個(gè)分析文件夾下的TFLs程序或Output缺失或多余的情況赖阻。
以我當(dāng)前公司的數(shù)據(jù)結(jié)構(gòu)為例蝶押，匯總程序如下。若讀者想要引用火欧，需結(jié)合項(xiàng)目文件結(jié)構(gòu)以及Tracker信息進(jìn)行更新棋电。
***1. Get file names in tracker (Company internal tool);
%global path_ana pi_sas;
%let path_ana = /xxx/xxx;
%let pi_sas = pi_xxx_xxx.sas;
%include "&path_ana./docs/pi/&pi_sas.";
%pi_amg757_bla_iss_90d_2023(
lib = work
,txtpath = &path_ana./docs/pi/
,outds = all
);
***2. Macro to get file names in folder;
%macro get_file(dirpath =, outdt =, type = sas );
%if "&dirpath." ne "" %then %do;
%local dirpath_tmp slash;
%let slash = %substr(%sysfunc(compress(&dirpath., : _ , a d)), 1, 1);
*Remove trailing slash;
%if "%substr(&dirpath., %length(&dirpath.),1)"= "&slash." %then %let dirpath_tmp=%substr(&dirpath.,1, %length(&dirpath.) -1);
%else %let dirpath_tmp = &dirpath.;
** Dopen--Get filepath;
data &outdt.;
fileres = filename("dirpath", "&dirpath_tmp");
dirid = dopen("dirpath");
num = dnum(dirid);
length direct filename filepath $200;
if dirid >0 and num >0 then do;
do i=1 to num;
direct = "&dirpath_tmp.";
filename = dread(dirid, i);
filepath = catx("&slash.", direct, filename);
%if %index(%upcase(&type), SAS) %then %do;
if strip(scan(filename, 2, "."))="sas" then output;
%end;
%if %index(%upcase(&type), RTF) %then %do;
if strip(scan(filename, 2, "."))="rtf" then output;
%end;
%if %index(%upcase(&type), XLSX) %then %do;
if strip(scan(filename, 2, "."))= "xlsx" then output;
%end;
%if %index(%upcase(&type), XML) %then %do;
if strip(scan(filename, 2, "."))= "xml" then output;
%end;
end;
end;
keep filename filepath;
proc sort;
by filename;
run;
%end;
%mend get_file;
***3. Macro to check folder file;
%macro check_folder_file(out_type = tables);
%local type;
%if %upcase(&out_type) = TABLES %then %let type = T;
%else %if %upcase(&out_type) = LISTINGS %then %let type = L;
%else %if %upcase(&out_type) = FIGURES %then %let type = F;
**3.1 Source folder;
%get_file(
dirpath = &path_ana./&out_type.
,outdt = source_pgm_&out_type.
);
proc sql noprint;
**in tracker not in folder;
create table source_in_tracker_&out_type. as
select output_number, base_output_name, program_name, programmer, tester
from tfls
where output_type = "&type." and program_name not in
(
select filename from source_pgm_&out_type.
)
;
**in folder not in tracker;
create table source_in_folder_&out_type. as
select *
from source_pgm_&out_type.
where filename not in
(
select program_name from tfls
)
;
quit;
**3.2 QC folder;
%get_file(
dirpath = &path_ana./&out_type./validation
,outdt = qc_pgm_&out_type.
);
proc sql noprint;
**in tracker not in folder;
create table qc_in_tracker_&out_type. as
select output_number, testing_program_name, tester
from tfls
where output_type = "&type." and testing_program_name not in
(
select filename from qc_pgm_&out_type.
)
;
**in folder not in tracker;
create table qc_in_folder_&out_type. as
select *
from qc_pgm_&out_type.
where filename not in
(
select testing_program_name from tfls
)
;
quit;
**3.3 Output folder;
%get_file(
dirpath = &path_ana./&out_type./output
,outdt = output_&out_type.
,type = rtf-xlsx
);
proc sql noprint;
**in tracker not in folder;
create table output_in_tracker_&out_type. as
select *
from tfls
where output_type = "&type." and output_name not in
(
select filename from output_&out_type.
)
;
**in folder not in tracker;
create table output_in_folder_&out_type. as
select *
from output_&out_type.
where filename not in
(
select output_name from tfls
where output_type = "&type."
)
;
**3.4 xml template folder (internal use);
%get_file(
dirpath = &path_ana./docs/templates
,outdt = tflgen_template
,type = xml
);
proc sql noprint;
**in tracker not in folder;
create table tflgen_template_in_tracker as
select *
from tfls
where tflgen_allow_code_generation = "Yes" and strip(tflgen_template_name)||".xml" not in
(
select filename from tflgen_template
)
;
**in folder not in tracker;
create table tflgen_template_in_folder as
select *
from tflgen_template
where filename not in
(
select strip(tflgen_template_name)||".xml" from tfls
where tflgen_allow_code_generation = "Yes"
)
;
quit;
%mend check_folder_file;
%check_folder_file(out_type = tables);
%check_folder_file(out_type = listings);
%check_folder_file(out_type = figures);
感謝閱讀， 歡迎關(guān)注：SAS茶談苇侵！
若有疑問(wèn)赶盔，歡迎評(píng)論交流！
梳理不易榆浓，轉(zhuǎn)載請(qǐng)注明出處 (by Jihai / SAS茶談)
