topic Re: Create a Read-Only Excel File from SAS in SAS Programming

Create a Read-Only Excel File from SAS

djbateman — Thu, 20 Aug 2020 19:30:50 GMT

I am trying to create a read-only Excel file from SAS. I have found a lot of methods that will do the trick, but I can't seem to get any of them to work.

I have tried using the X commands:

x 'attrib +r "S:\cdm\Programming\OutputFile_&sysdate9..xlsx"';

I tried r+ and +r with no luck.

I have tried the protect_worksheet='no' option in ODS EXCEL with no luck.

I have tried applying at password as found here: https://support.sas.com/kb/31/328.html with no luck.

I think I have narrowed it down to altering a macro that I am already using that just needs another parameter, but I don't know the VBA syntax.

%macro convert_files(default=,ext=);
	data _null_;
		file "'&default\temp.vbs'";
		put "set xlapp = CreateObject(""Excel.Application"")";
		put "set fso = CreateObject(""scripting.filesystemobject"")";
		put "set myfolder = fso.GetFolder(""&default"")";
		put "set myfiles = myfolder.Files";
		put "xlapp.DisplayAlerts = False";
		put " ";
		put "for each f in myfiles";
		put "If f.Size < 70000 And fso.GetExtensionName(f)=""&ext"" Then";
		put "  f.Delete True";
		put "    End If";
		put "  Next";
		put " ";
		put "for each f in myfiles";
		put "  ExtName = fso.GetExtensionName(f)";
		put "  Filename= fso.GetBaseName(f)";
		put "    if ExtName=""&ext"" then";
		put "           set mybook = xlapp.Workbooks.Open(f.Path)"; 
		put "           xlapp.Visible = false";
		put "           mybook.SaveAs ""&default.\"" & Filename & "".xlsx"", 51";
		put "    End If";
		put "  Next";
		put "  mybook.Close";
		put "  xlapp.DisplayAlerts = True";
		/* Removes original files */
		put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";
		put " xlapp.Quit";
		put " Set xlapp = Nothing";
		put " strScript = Wscript.ScriptFullName";
		put " FSO.DeleteFile(strScript)"; 
	run; 
 
	x "cscript ""&default\temp.vbs""";
%mend;

This macro is what we use to convert XML files to XLSX. There is a SaveAs line in the macro where I specify the filename and the file type. After that is supposed to be allowance to specify a password, backup, and read-only option, but I don't know how to alter it to get what I want. Does anyone have any suggestions for my alteration or a different way to make a file read only?

Re: Create a Read-Only Excel File from SAS

LeonidBatkhan — Thu, 20 Aug 2020 22:25:50 GMT

Hi djbateman,

You can define your file permissions in the filename statement:

filename f "&default\temp.vbs" permission='A::u::rwx,A::g::r-x,A::o::---';

Then use in your data step as

data _null_;
   file f;
   . . .

For more information on assigning files permissions programmatically see my blog post Let SAS write batch scripts for you.

Hope this helps.

Re: Create a Read-Only Excel File from SAS

djbateman — Thu, 20 Aug 2020 20:52:19 GMT

Thanks for your input. I didn't quite follow your suggestion, but I was able to get my issue resolved. I was able to use the +r attribute with the X statement. I just had to remove the single quotes:

x attrib +r "S:\cdm\Programming\OutputFile_&sysdate9..xlsx";

Re: Create a Read-Only Excel File from SAS

ChrisNZ — Fri, 21 Aug 2020 02:42:29 GMT

How does this work under windows? There are no group and other users.

Re: Create a Read-Only Excel File from SAS

LeonidBatkhan — Fri, 21 Aug 2020 15:40:22 GMT

There are groups in Windows. I assume that OTHER is not the USER and not a GROUP where the USER belongs to. I have not played with this on Windows though. Here is the link to the FILENAME PERMISSION= option documentation for Windows .