BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
djbateman
Lapis Lazuli | Level 10

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?

1 ACCEPTED SOLUTION

Accepted Solutions
LeonidBatkhan
Lapis Lazuli | Level 10

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.

 

View solution in original post

4 REPLIES 4
LeonidBatkhan
Lapis Lazuli | Level 10

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.

 

djbateman
Lapis Lazuli | Level 10
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";
ChrisNZ
Tourmaline | Level 20

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

LeonidBatkhan
Lapis Lazuli | Level 10

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 .

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 4 replies
  • 1727 views
  • 1 like
  • 3 in conversation