Powershell command using Call System - replace text in xml file

[Reeza]

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;

[mkeintz · Accepted solution]

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""";

[Reeza]

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. 

[Ksharp]

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' ;