Hi Folks,
I cannot get my mind around an issue I am having with a call of a macro var within a %sysexec -statement.
I am executing several shell commands within one %sysexec - statement. Now, I'd like to loop this statement by using a macrovar in the "SELECT * FROM &TABLE" - part.
However, every time I execute this statement I do not get any error message, but there is nothing being written to the "test.txt" file either.
(<ssh credentials> and <mysql credentials> is not the problem).
I executed this step by replacing the &TABLE-macrovar with the real table name (i.e. "testtable") and it worked just fine. I am pretty sure, it has got to do with masking/unmasking the macrovar. Can someone help me out with this issue?
I tried using %sysfunc(symget("TABLE")), but the symget function is not allowed with %sysfunc.....
One problem could be, that the mysql - statement is in single quotes. But unfortunatelly, I cannot replace them with double quots, because then, the shell statement does not work anymore.
%LET TABLE = testtable;
%MACRO get_data_from_mysql;
%sysexec %str(ssh <SSH credentials>
'mysql <mysql credentials> -e
"USE <schema>; SELECT * FROM &TABLE. ";'
> /path/to/my/folder/test.txt)
;
%mend;
%get_data_from_mysql;
Thanks,
FK1
Here's a common way to control masking and unmasking. It doesn't guarantee to solve all your problems, but it should get you through the first one at least:
%sysexec %unquote(%str(ssh <SSH credentials>
%'mysql <mysql credentials> -e "USE <schema>; SELECT * FROM &TABLE. ";%'
> /path/to/my/folder/test.txt) )
;
Here's a common way to control masking and unmasking. It doesn't guarantee to solve all your problems, but it should get you through the first one at least:
%sysexec %unquote(%str(ssh <SSH credentials>
%'mysql <mysql credentials> -e "USE <schema>; SELECT * FROM &TABLE. ";%'
> /path/to/my/folder/test.txt) )
;
Hi there,
I do want to pick up on this topic again, as I am still having troubles:
Like last time I want to execute a shell command from within SAS, but this time using %unquote(%nrbquote(.....)) :
%sysexec %unquote(%nrbquote(
ssh johndoe@some.server.host
<MYSQL credentials> > /home/some/path/test42.txt
<<< "select * from information_schema.TABLES WHERE table_schema = 'some_value' "));
I do get neither Errors nor Warnings, however, when I %PUT the &SYSRC. I can see, it has a value of "2", i.e. ERROR-message.
I do know for sure that the shell command is correct, as I tested it interactively in the shell prompt and it worked just fine, meaning: it "filled" the textfile "test42".
What's going on herre?
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.