DATA Step, Macro, Functions and more

Mask/Unmask Macrovar in %sysexecute - statement

Accepted Solution Solved
Reply
Highlighted
Contributor FK1
Contributor
Posts: 26
Accepted Solution

Mask/Unmask Macrovar in %sysexecute - statement

[ Edited ]

 

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


Accepted Solutions
Solution
a week ago
Super User
Posts: 5,608

Re: Mask/Unmask Macrovar in %sysexecute - statement

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

View solution in original post


All Replies
Solution
a week ago
Super User
Posts: 5,608

Re: Mask/Unmask Macrovar in %sysexecute - statement

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

Contributor FK1
Contributor
Posts: 26

Re: Mask/Unmask Macrovar in %sysexecute - statement

Posted in reply to Astounding
Thanks Astounding! That's it! You are astounding :-)
Contributor FK1
Contributor
Posts: 26

Re: Mask/Unmask Macrovar in %sysexecute - statement

[ Edited ]

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?

 

 

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 109 views
  • 0 likes
  • 2 in conversation