* 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 */