·Î±×ÀÎ   |  È¸¿ø°¡ÀÔ  |  »çÀÌÆ®¸Ê  |  Contact Us
  ¾ÆÀ̵ð ÀúÀåÇϱâ
 
Ȩ > SAS Tech & Tip > SAS °í¼ö
[¿¢¼¿] Àб⠾ÏÈ£°¡ ºÎ¿©µÈ ¿¢¼¿ ÆÄÀÏ Àбâ 2017.06.22
¹é½Â¹Î 485 0
http://www.mysas.co.kr/SAS_tiptech/a_all.asp?b_no=7336&gotopage=1&con=subject&keyword=&cmd=content&bd_no=33&gubun=

* Àб⠾ÏÈ£°¡ ºÎ¿©µÈ ¿¢¼¿ ÆÄÀÏ Àбâ;

* 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  */
 
 
 
 
 [DS2] DS2ÀÇ matrix ±â´ÉÀ» Ȱ¿ëÇÑ È¸±ÍºÐ¼® ¼öÇàÇϱâ
 [ODSBABLE] »ç¿ëÀÚ Ãâ·Â ÅÛÇø´ »ý¼ºÇϱâ