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' ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.