DATA Step, Macro, Functions and more

Powershell command using Call System - replace text in xml file

Accepted Solution Solved
Reply
Super User
Posts: 19,869
Accepted Solution

Powershell command using Call System - replace text in xml file

[ Edited ]

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 Smiley Happy

 

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

Accepted Solutions
Solution
‎05-23-2017 06:48 PM
Trusted Advisor
Posts: 1,022

Re: Powershell command using Call System - replace text in xml 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""";

View solution in original post


All Replies
Solution
‎05-23-2017 06:48 PM
Trusted Advisor
Posts: 1,022

Re: Powershell command using Call System - replace text in xml 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""";

Super User
Posts: 19,869

Re: Powershell command using Call System - replace text in xml 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. 

Super User
Posts: 10,045

Re: Powershell command using Call System - replace text in xml file

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 155 views
  • 2 likes
  • 3 in conversation