BookmarkSubscribeRSS Feed
SalmaTas
Fluorite | Level 6

i want to export excel sheet with password, for example this is the data i want to export: 

data test;
input user $10.;
datalines;
user1
user2
user3
user4
user5
;
run;

 

and then i want to export it to the specific location:

proc export data=test
outfile="C:\Users\Data\test.xls"
dbms=excel replace;
run;

 

where do i insert the password part in the export? 

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

http://support.sas.com/kb/48/682.html

 

You can't do it directly in the generation of the file.  

SalmaTas
Fluorite | Level 6
ok, thanks
jitendra_pandey
Calcite | Level 5

VBScript method might work it one has to password protect the files manually.  This is not a fool-proof method for a batch execution where you have dozens of file to passowrd protect and distribute.  What if the VBScript does not at all run?  You will end-up distributing unprotected file.  SAS does not provide any guarantee.  Please read the fineprint.

 

I believe this is time when SAS should cease its reliance on these VBScripts and come up with native mehtods.  Examples are 1) Generating rich Excel reports, 2) Converting XLS (OpenXML document) to native XLS or XLSX file format, 3) Password protection of Excel files as metioned in this topic.

Thanks and regards,
Jitendra Pandey
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Please note the date on this topic is 2015.  Open a new question if you need further information.

As for your points:

"VBScript method might work it one has to password protect the files manually.  This is not a fool-proof method for a batch execution where you have dozens of file to passowrd protect and distribute.  What if the VBScript does not at all run?  You will end-up distributing unprotected file.  SAS does not provide any guarantee.  Please read the fineprint." - of course, VBA is third part and so not under SAS, nor is it actually mentioned in this thread?

 

"I believe this is time when SAS should cease its reliance on these VBScripts and come up with native mehtods.  Examples are 1) Generating rich Excel reports, 2) Converting XLS (OpenXML document) to native XLS or XLSX file format, 3) Password protection of Excel files as metioned in this topic." - SAS does not have a reliance on VB, it is the users have their own desires, much like any third party tool.  

SAS does provide various methods to generate rich Excel files - tagsets.excelxp/msoffice2k, libname excel, proc export etc.

Why shoud SAS, with no involvement in either tech, provide a conversion between OpenXML and Open Office?

Password protection on Excel files (at least some time back) was a joke.  Anyone relying on that should really think about their processes.  Secure documents should be sent by the appropriate methodology - SFTP for instance, web portals and such like, with proper encryption, access control and auditing.  

jitendra_pandey
Calcite | Level 5

My bad, I always thought VBA is owned by SAS Institute.  And oh, I never knew files generated from ExcepXP are in native Excel format and neither did I know that MSOffice2K supports all the formatting options that ExcelXP does.  And this is also new to me that libname Excel and PROC EXPORT do support all the formatting options that ExcelXP does!!

 

Password protection on Excel files was a joke with Microsoft Excel versions prior to 2007.  Note this, that Microsoft has been using AES-128 bit encryption with Excel 2007 and later so it not a joke anymore.  Now, that could just be one layer in securing your email report distribution in combination with 2) encrypting an entire email message at your corporate Exchange server and 3) using Transport Layer Security or TLS for transmission and this would not be a joke of slightest nature.

Thanks and regards,
Jitendra Pandey
RW9
Diamond | Level 26 RW9
Diamond | Level 26

VBA - Visual Basic for Applications, is ownder by Microsoft and is part of thier Office Suite.

Tagsets output are XML, not native XLSX which is actually a ZIP containing XML and folders.  However this XML is perfectly readable in Excel, i.e. it knows what it is.  It has a lot of customisation.  MSOffice2k is the older varient which some still use.

libname excel is limited in what formatting, however can create native XLSX files (i meant the following one not libname)

ods excel is the newset technology, this can create native XLSX files with various options:

communities.sas.com/community/support-communities

 

You could also use SAS office plugin - no experience of that though.

 

To note, instead of sending files by email, post to a secure web portal - avoids large files being sent/blocked (and messing up inboxes) and adds all the necessary security in - with a newtork address you can just save files there and then email out that XYZ output is updated. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 7239 views
  • 0 likes
  • 3 in conversation