topic Re: Adding network file path with spaces to X command in SAS Procedures

Adding network file path with spaces to X command

basinbasin — Thu, 11 Aug 2016 08:52:10 GMT

I have to send some parameters to an external .exe using the x command. Because I may be sending parameters that are fed from macro variables, and may also contain single quotes, I enclose the entire string within a quote statement. (Some of the parameters may contain spaces, which is why they are enclosed in double quotes):

x %sysfunc(quote(
c:\windows\temp\myapplication.exe /loginandget
"parameter 1"
parameter2
&Param3var.
"parameter 4"
));

This works correctly as intended. The problem is that I'd like to move myapplication.exe to a network filepath that contains spaces. I've tried various combinations of double quotes, moving the %sysfunc(quote to different places in the string, adding %STR("") and nothing seems to work:

x %sysfunc(quote(
\\my share\my subfolder\myapplication.exe /loginandget
"parameter 1"
parameter2
&Param3var.
"parameter 4"
));

When testing, I've replaced x with %let x = and then added a %put x; at the bottom to see how SAS parses the string, and results that look like they should work, don't when I change the code back to the x command!

Any advice gratefully received. Many thanks.

Re: Adding network file path with spaces to X command

Kurt_Bremser — Thu, 11 Aug 2016 09:03:46 GMT

Build the whole command string in a data _null_ step and use call system() to execute it.

That way you can run the data step repeatedly during testing (replacing the call system() with a put or have the string written to a dataset), build the string element-by-element and check at every stage if you did it correctly.

Once the result is as wanted, implement the call system().

It's much easier to deal with multiple (and different) quotes inside a data step, where you can use single quotes to build a string containing doubles, and vice versa, and then concatenate it all together. Don't forget to use length so that your string variables are long enough.

Re: Adding network file path with spaces to X command

LinusH — Thu, 11 Aug 2016 09:04:06 GMT

Personally, I would find a bit easier to create the whole string in a data set variable, and perhaps execute it via CALL SYSTEM.

See also another thread with a similar issue, and various suggestions....:

https://communities.sas.com/t5/Base-SAS-Programming/resolving-macro-variable-inside-single-quotes/td-p/67294

Re: Adding network file path with spaces to X command

RahulG — Thu, 11 Aug 2016 09:06:37 GMT

You need to also put the .exe command in quotes

"\\my share\my subfolder\myapplication.exe"

I have not tested, but it should work.

Re: Adding network file path with spaces to X command

RW9 — Thu, 11 Aug 2016 09:18:28 GMT

Out of interest, what is this "application" to do? In my experience I have never seen a need to call external programs from within a SAS session - its complicated and never necessary. By loginandget I assume this is accessing a system and retrieving some information? If so then address the process of data transfer - how does your other system deliver data, can it be scheduled to dump data to specified location, which your SAS program can access, could you directly interface to the data in that system etc. Many ways of doing things. The reason being is 5 or 6 years down the line when you have moved on, someone else will look at that bit of code and an exe file which has stopped working and then have to try to sort it all out. Keep it as simple as possible, use the two systems you already have - data source and your program - don't add in additional components.

Re: Adding network file path with spaces to X command

basinbasin — Fri, 12 Aug 2016 10:56:44 GMT

Thanks for your help everyone. I liked the idea of building and testing the output in a data _null_ statement, and then using call execute to call it. However, I still couldn't get it to work. Although my path was surrounded in double quotes (or double double quotes) in the sample sent to the log, I still ended up with "invalid path" errors. Either that, or it was obvious that the path was being truncated at the first space in the path name despite being enclosed in quotes.

In the end, I ended up "cheating" and converted the file path to my exe to DOS format in my code. So:

\\my share\my subfolder\myapplication.exe becomes \\MYSHAR~1\MYSUBF~1\myapplication.exe

So no need for quotes, and it works as intended. The code I've written is sort of sample code which discusses using X, Filename... pipe and rc=system(...) (but not call execute annoyingly) so I also needed a solution that didn't require bespoke recoding for each call method.

Yes, I'd love to get the source data from a local database, but the number of articles and search results for Filename... URL and using cURL with SAS suggests that I'm not the only one having to "hack" getting web-based data, which is what the application does.

Re: Adding network file path with spaces to X command

RW9 — Fri, 12 Aug 2016 11:46:36 GMT

Well, if your access a web database, and you can't directly access it (say with proc http), then I would seperate the processes. Process 1 would be the connecting to a database, downloading data to a csv file. End of process - make sure all that works, and is validated and schedule it. Then a seperate process, you SAS program, would load that CSV. No need to run one from the other.

Re: Adding network file path with spaces to X command

basinbasin — Fri, 12 Aug 2016 12:41:01 GMT

It's the old argument, really, of flexibility vs robustness, I guess.

We use a vendor's external website, which we log into. They actually have a webpage on their site where you can type SQL into the webpage, hit the button, and the results are returned in an HTML table, or flat file, or excel spreadsheet.

So the flexibility is that we can manipulate internal data using SAS, and then utilise that data to use within the SQL that we then send using the external application into the webtool. As a really simple example:

PROC SQL;

SELECT DISTINCT "'"||trim(CITY)||"'"
 INTO :city
 FROM some_local_table (Obs=1);
 QUIT;

 %put &city.;

x %sysfunc(quote(
\\my share\my subfolder\myapplication.exe /loginandget
"parameter 1"
parameter2
&Param3var.
"select col1, col2 from vendors_remote_table where city=&city."
));

Yes it's a horrible hack, but needs must.... The vendor also does send us overnight batch files which are uploaded into a SAS Server, but lately, this process fails as often as it works.

But as you say, if we're always going to be getting the same data back from the vendor's site, no reason why we can't schedule the application to run outside of SAS, and save the output somewhere, and then schedule a SAS job to pick up that data and do things with it.

Re: Adding network file path with spaces to X command

kleinhev — Wed, 01 Nov 2017 02:04:16 GMT

Try to use single quotes around double quotes (or double quotes around single quotes like for this x command:

x "D:\Bla Bla\OneDrive\My OneDrive Folder\Bla analysis\My bla analysis results.xlsx";

: does not work

x '"D:\Bla Bla\OneDrive\My OneDrive Folder\Bla analysis\My bla analysis results.xlsx"';

: works