* Àб⠾ÏÈ£°¡ ºÎ¿©µÈ ¿¢¼¿ ÆÄÀÏ Àбâ; * 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 */
|