topic Re: Statement to order my PC to open xlsx file in Excel program? in SAS Programming

Statement to order my PC to open xlsx file in Excel program?

braam — Wed, 09 Oct 2019 09:58:17 GMT

Dear All, I'm wondering if there is such a SAS statement/command to order my PC to open xlsx file using Microsoft Excel. After running some analyses in SAS, I report outputs in xlsx files. Obviously I would like to open them. But do I have to use another program such as Windows explorer to open the file? Or is there a way to do so in SAS? Thanks!

Re: Statement to order my PC to open xlsx file in Excel program?

andreas_lds — Wed, 09 Oct 2019 11:02:20 GMT

Depends on the computer your sas-session is running on. If it is running on your computer try using the x-command or call system. If it is running on a server opening local files is not possible.

Re: Statement to order my PC to open xlsx file in Excel program?

Kurt_Bremser — Wed, 09 Oct 2019 11:10:37 GMT

Only if

SAS runs locally on your desktop
you have XCMD enabled

Re: Statement to order my PC to open xlsx file in Excel program?

braam — Wed, 09 Oct 2019 12:38:01 GMT

@andreas_lds @Kurt_Bremser Thanks for your comments. Could you please give me some keywords so that I can search on google? Sorry that I actually can't understand your answers.

Re: Statement to order my PC to open xlsx file in Excel program?

Kurt_Bremser — Wed, 09 Oct 2019 13:25:18 GMT

First we have to determine where your SAS session runs. From an earlier post of yours I take it that you actually use Display Manager; if that is running on your desktop (you are not logging on to a server and start Base SAS there), you can use the X statement to simply run your file (as Windows will automatically look for the correct application and start that):

proc export
  data=mylib.myset
  outfile="C:\some_path\excelfile.xlsx"
  dbms=xlsx
;
run;

options noxsync noxwait;
X "C:\some_path\excelfile.xlsx";

Re: Statement to order my PC to open xlsx file in Excel program?

Reeza — Wed, 09 Oct 2019 14:48:41 GMT

What type of SAS are you using, Studio, EG, Base? Base and EG have an option to open results as they're generated which is really the easiest option here, though it does depend a bit on how you're creating the files.

If you're not using ODS you may need the X command, but that's not available in all systems.

@braam wrote:

Dear All, I'm wondering if there is such a SAS statement/command to order my PC to open xlsx file using Microsoft Excel. After running some analyses in SAS, I report outputs in xlsx files. Obviously I would like to open them. But do I have to use another program such as Windows explorer to open the file? Or is there a way to do so in SAS? Thanks!

Re: Statement to order my PC to open xlsx file in Excel program?

ballardw — Wed, 09 Oct 2019 15:23:39 GMT

@Kurt_Bremser wrote:

First we have to determine where your SAS session runs. From an earlier post of yours I take it that you actually use Display Manager; if that is running on your desktop (you are not logging on to a server and start Base SAS there), you can use the X statement to simply run your file (as Windows will automatically look for the correct application and start that):
proc export
  data=mylib.myset
  outfile="C:\some_path\excelfile.xlsx"
  dbms=xlsx
;
run;

options noxsync noxwait;
X "C:\some_path\excelfile.xlsx";

Actually in Display Manager the first thing I would check are preferences: From the menu or tool bar: Select Tools>Options>Preferences. Go the Results tab and make sure that the "View results as they are generated" is checked.

If that is checked then tell us what application is currently opening the file as that means that your Windows settings is defaulting to some other application and that Windows setting needs to change.

Re: Statement to order my PC to open xlsx file in Excel program?

braam — Wed, 09 Oct 2019 17:32:00 GMT

@Reeza Thanks for your answer. The code from @Kurt_Bremser worked for me!

I use SAS Base. Could you please let me know what I can do other than the X command? I think I'll use this type of work quite often. Thanks.

@Kurt_Bremser Thanks for your suggestion. Again, it worked for me. But another issue I face now is that the directory that stores my excel file inlcudes a blank space like "D:\sas\Project AB\file.xlsx". When I run the code you shared, I got a pop-up message "Windows cannot find 'D:sas\Project AB\file.xlsx'. Make sure you typed the name correctly, and then try again. I'm sure it's coming from the blank, because it worked for me when I moved the file in "D:\sas". Do you know how to solve this issue? Thanks again.

Re: Statement to order my PC to open xlsx file in Excel program?

Reeza — Wed, 09 Oct 2019 17:31:30 GMT

The setting that @ballardw specified is correct - it will automatically open up the results as they're generated.

Re: Statement to order my PC to open xlsx file in Excel program?

braam — Wed, 09 Oct 2019 17:33:46 GMT

Yes, I think it's a good way. But I just wanted to open results only when I would like to have a look at. For example, conduct some a set of analyses first, save them into one excel file, and open the file.

Re: Statement to order my PC to open xlsx file in Excel program?

Reeza — Wed, 09 Oct 2019 17:36:33 GMT

Makes sense, I prefer ODS EXCEL myself these days, seems like it provides enough of the style + data simplicity that works.

Re: Statement to order my PC to open xlsx file in Excel program?

Tom — Wed, 09 Oct 2019 17:39:08 GMT

Make sure you are generating valid Windows syntax in your X command. In particular you need quotes around names with embedded spaces. (Why do people create filenames with spaces in them to begin with?).

X """C:\some_path\excelfile.xlsx""";

Re: Statement to order my PC to open xlsx file in Excel program?

braam — Wed, 09 Oct 2019 17:42:15 GMT

Ah got your piont. So output to excel using ODS and use the x command? Thanks.

@Tom Thanks for your answer. It worked for me! Now I realized that it's not a good habit to have spaces in file/directory names. Thanks again.

Re: Statement to order my PC to open xlsx file in Excel program?

Kurt_Bremser — Thu, 10 Oct 2019 06:28:36 GMT

@braam wrote:

Now I realized that it's not a good habit to have spaces in file/directory names.

See Maxim 44.

One of the biggest blunders of Microsoft (and they committed so many nobody even attempts to count them) was naming one of the most important system directories "Program Files" instead of just "programs". That's what happens when garage tinkerers think they have a clue about computers.

Re: Statement to order my PC to open xlsx file in Excel program?

braam — Sat, 19 Oct 2019 21:28:37 GMT

Dear Tom, could you explain the logic behind """ (three times)? Any link/document would be appreciated as well. I have no clue to dig in.

Re: Statement to order my PC to open xlsx file in Excel program?

Tom — Sat, 19 Oct 2019 21:40:54 GMT

Embedded quotes need to be doubled. Some examples:

'Don''t'
"He said, ""Hello""."

Windows allows spaces in directory and filenames. Any name that might have a space in it needs to be quoted since space is the natural delimited between tokens on the command line.