Wednesday, June 21, 2017

Import password protected EXCEL into SAS


Click Here

%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(j:\access\accpcff\excelfiles\passpro.xlsx, /* name of the xlsx 2007 file */
          c:\sastest\nopass.xlsx,  /* temporary xls file for translation for import */
    mypass,               /* password of the excel spreadsheet          */
           work.temp1,  /* name of the sas dataset you want to write */
           sheet1,     /* name of the sheet */
           yes) ;     /* getnames  */

19 comments:

Shalini said...

Really Good blog post.provided a helpful information.I hope that you will post more updates like this.
Digital marketing company in Chennai

Neha Reddy said...

Useful Information, your blog is sharing unique information....
Thanks for sharing!!!
sas value added reseller
software var north america
sap mbe var in north america
sap service provider
top software service providers in North America

logistic-solutions said...


Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
corporate social responsibility services in NA

Ancy merina said...
This comment has been removed by the author.
Pamela Anastacia said...

Great article! Well written and one of the most comprehensive I have seen on the net. Keep up the good work!

depobola

logistic-solutions said...

Nice post ! Thanks for sharing valuable information with us. Keep sharing.
emc software vendors

logistic-solutions said...

Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
mbe oracle Partner
mbe microsoft gold partner

logistic-solutions said...

Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
mbe sas partner
sap strategic consulting services

logistic-solutions said...

Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
sas value added reseller
supply diversity services

logistic-solutions said...

Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
sas value added reseller
supply diversity services

Clinnovo said...

it is wonderfull blog thanks for sharing
SAS Certification

logistic-solutions said...

Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
sas value added reseller
corporate social responsibility services in NA

logistic-solutions said...

Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
MBE Oracle Reseller
sas implementation services

logistic-solutions said...

Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
sap mbe reseller
sap csr

logistic-solutions said...

Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
SAP reseller
Software Reselling services
mbe supplier in software services
mbe software reseller services
bmc software vendors
MBE Oracle Reseller
Software reseller services in north america
sap minority reseller

Kyra Anderson said...

It’s truly nice blog and we offer activation and support service for MS Office.
setup.office.com
www.office.com/setup

logistic-solutions said...


Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site.
SAS and SAP implementation services
sas implementation services
sap implementation services
sap installation and configuration service
sas installation and configuration service
sas implementation services in north america
sap customer relationship management service in NA

Unknown said...

Indeed very helpful!!!!
Thank you so much for sharing this macro.

Video Teraveloka said...

Penggemar Judi Online gak boleh lewatin info penting satu ini. Kali ini panduan tentang Deposit Pulsa XL Telkomsel ke akun Sbobet Online kamu. Hah ? Main judi pake pulsa ? Memang bisa ? (Baca Selengkapnya Disini...)