topic Re: Proc Export to Password Protected Excel in SAS Programming

Proc Export to Password Protected Excel

hwangnyc — Thu, 28 Jul 2016 14:41:44 GMT

Hi All,

I'm trying to export a dataset to a excel file that has a password protect feature enabled. Here is the code that I am using:

PROC EXPORT DATA = contactOUT
OUTFILE = "S:\counselor.xlsx"
DBMS=xlsx REPLACE;
Sheet = "Counselor";
QUIT;

I get this error when I try to run it:

Error creating XLSX file -> S:\CommunityHealth\DPHO-Harlem\Research, Evaluation, and
Planning\Henry\EHACE\Reports\CHE Monthly Report\counselor.xlsx . It is either not an Excel
spreadsheet or it is damaged. Error code=8014900A
Requested Output File is Invalid

ERROR: Export unsuccessful. See SAS Log for details.

Any way to input the password into the code so it can write to it?

Thanks!

Re: Proc Export to Password Protected Excel

RW9 — Thu, 28 Jul 2016 15:02:47 GMT

It is nothing to do with the password protected Excel file as far as I can see, the password is only on opening the file that it is a problem, and you have specified REPLACE, so it should just overwrite what is there, assuming you have permission to write there, and that path exists and has no special characters in. I suspect that you dont have access to that area, or something similar, what software are you using - is it Enterprise Guide, Visual Analytics, University Edition etc? For UE, you paths should be unix style and relative.

Also to note in any circumstance the path:

S:\CommunityHealth\DPHO-Harlem\Research, Evaluation, and Planning\Henry\EHACE\Reports\CHE Monthly Report\counselor.xlsx

Is really not to be recommended. Paths really should not contain spaces, special characters (such as commas) as this just causes problems for programmers.

Re: Proc Export to Password Protected Excel

hwangnyc — Thu, 28 Jul 2016 15:11:19 GMT

Hi RW9,

Thanks for the tips, especially with the filenames. But I think the password protect is causing the issue. When I remove the password from the excel file it outputs just fine. I think the replace option is for replacing the SHEET and not the file.

Re: Proc Export to Password Protected Excel

RW9 — Thu, 28 Jul 2016 15:21:18 GMT

Ah yes, looking at the SAS guidance:

http://support.sas.com/kb/20/923.html

It does seem like it will try to replace the named range if it exists. Well, from memory, no you can't do much with a password protected Excel file. but you could just use the fdelete command to delete the file before exporting:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000245893.htm

Re: Proc Export to Password Protected Excel

LinusH — Thu, 28 Jul 2016 18:36:51 GMT

Just don't use password protected files in BI solutions, it just cause headache and are not particularly safe. Find other means to protect your data.

Re: Proc Export to Password Protected Excel

hwangnyc — Thu, 28 Jul 2016 19:13:21 GMT

Thanks for the response. Do you have any suggestions as to how to better protect the information if I remove the password? I export this data out to the excel file on a daily basis and only want a select number of people to be able to view the file. It's more a safe guard that someone does not accidently open the file being exposed to the information.

Re: Proc Export to Password Protected Excel

RW9 — Thu, 28 Jul 2016 20:06:02 GMT

Don't use Excel full stop. Totally unvalidated, unsafe, unstructured file format. Publish your results to an access restricted web address, Sharepoint for example. Use PDF or something to make it hard to edit.