Mask/Unmask Macrovar in %sysexecute - statement

[FK1]

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

[Astounding · Accepted solution]

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

[FK1]

Thanks Astounding! That's it! You are astounding 🙂

[FK1]

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?