I have an XML file and I need to update a single variable in the XML files (~100 files).
I've managed to get the command working in powershell, but can't seem to figure out how to pass this to SAS to execute.
I'm having trouble with getting the correct number of quotation marks for my macro variables to resolve and passing this to the system somehow.
My code so far is below and I'm attaching the xml file for testing as well, with a TXT extension. It has a single word in it, Spatial_Area that would need to be updated - this is only for testing.
I feel like the solution is obvious but it's the end of the day after meetings and my brain is fried so any assistance is appreciated.
If you have a SAS solution to replace the field using SAS, I wouldn't be averse to that either, I just need to finish this at this point before the long weekend 🙂
%let path_file = C:\_localdata\Spatial_Area.xml;
data CHECK;
application_exe = "powershell.exe -command ";
command = '(gc "&path_file") | ForEach-Object { $_ -replace "Spatial_Area", "CD 1" } | sc "&path_file"';
cmd = application_exe || '"' || command || '"';
*Place note in log;
putlog "NOTE-Processing command" cmd;
*Execute command;
call system(cmd);
RUN;
The single quote is masking resolution of the &path_file macrovar. Even though it occurs in double quotes, it's inside the singel quotes.
So use double quotes throughout, noting that internal double quotes must be "escaped" by doubling each of them. I.e. instead of
command = '(gc "&path_file") | ForEach-Object { $_ -replace "Spatial_Area", "CD 1" } | sc "&path_file"';
use
command = "(gc ""&path_file"") | ForEach-Object { $_ -replace ""Spatial_Area"", ""CD 1"" } | sc ""&path_file""";
The single quote is masking resolution of the &path_file macrovar. Even though it occurs in double quotes, it's inside the singel quotes.
So use double quotes throughout, noting that internal double quotes must be "escaped" by doubling each of them. I.e. instead of
command = '(gc "&path_file") | ForEach-Object { $_ -replace "Spatial_Area", "CD 1" } | sc "&path_file"';
use
command = "(gc ""&path_file"") | ForEach-Object { $_ -replace ""Spatial_Area"", ""CD 1"" } | sc ""&path_file""";
Thanks!
I ended up having to add single quotes around the path as well because the file names had spaces. Without spaces it works fine, but with the spaces it had issues.
Additionally, rather than the ForEach I did find that PowerShell has a REPLACE() method (term?) that worked and the syntax for that was easier to manage.
Again, thanks @mkeintz & @Ksharp.
@Ksharp My colleague did go down the bat file route and that did work as well, so thanks for that suggestion. She learned a lot from the process.
Reeza,
You could save these os command as a BAT file (e.g. make a text file which has BAT extension name
and contain all the command you need, it is easy to do by data step ),and execute it at SAS side.
E.X.
c:\temp\xx.bat
has the os command you need like :
c:\temp\powershell.exe -command (gc "&path_file") | ForEach-Object
can execute it once for all.
x 'c:\temp\x.bat' ;
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.
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.