topic Mask/Unmask Macrovar in %sysexecute - statement in SAS Programming

Mask/Unmask Macrovar in %sysexecute - statement

FK1 — Wed, 06 Dec 2017 17:43:00 GMT

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

Re: Mask/Unmask Macrovar in %sysexecute - statement

Astounding — Wed, 06 Dec 2017 17:49:38 GMT

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

Re: Mask/Unmask Macrovar in %sysexecute - statement

FK1 — Wed, 06 Dec 2017 20:14:55 GMT

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

Re: Mask/Unmask Macrovar in %sysexecute - statement

FK1 — Thu, 14 Dec 2017 15:11:48 GMT

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?