* 읽기 암호가 부여된 엑셀 파일 읽기;
* Import password protected EXCEL into SAS;
* 출처 : http://sasstatistics.blogspot.kr/2017/06/import-password-protected-excel-into-sas.html;
%macro readpass(xlsfile1,xlsfile2,passwd,outfile,sheetname,getnames);
options macrogen symbolgen mprint nocaps; options noxwait noxsync;
/* %* we start excel here using this routine here *; */
/* 엑셀 공백 문서 열기 */
filename cmds dde 'excel|system';
data _null_;
length fid rc start stop time 8;
fid=fopen('cmds','s');
if (fid le 0) then do;
rc=system('start excel');
start=datetime();
stop=start+20;
do while (fid le 0);
fid=fopen('sas2xl','s');
time=datetime();
if (time ge stop) then fid=1;
end;
end;
rc=fclose(fid);
run;
quit;
/* %* then we open the excel sheet here with its password *; */
/* 읽기 암호가 부여된 엑셀파일 읽기 */
filename cmds dde 'excel|system';
data _null_;
file cmds;
put '[open("'"&xlsfile1"'",,,,"'"&passwd"'")]';
run;
/* %* then we save it without the password *; */
/* 암호 없이 새로운 문서로 저장 */
data _null_;
file cmds;
put '[error("false")]';
put '[save.as("'"&xlsfile2"'",51,"")]';
put '[quit]';
run;
/* %* Then we import the file here *; */
/* 새로운 엑셀파일 읽기 */
proc import datafile="&xlsfile2" out=&outfile dbms=xlsx replace;
%* sheet="%superq(datafilm&i)";
sheet="&sheetname";
getnames=&getnames;
run; quit;
/* %* then we destroy the non password excel file here *; */
/* 신규로 생성한 엑셀 파일(암호가 제거된) 삭제 */
systask command "del ""&xlsfile2"" ";
proc contents data=&outfile varnum;
run;
%mend readpass;
%readpass(d:\BACK pass test.xlsx, /* name of the xlsx 2007 file */
d:
opass.xlsx, /* temporary xls file for translation for import */
test, /* password of the excel spreadsheet */
work.back, /* name of the sas dataset you want to write */
sheet1, /* name of the sheet */
yes) ; /* getnames */
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.