BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
FK1
Lapis Lazuli | Level 10 FK1
Lapis Lazuli | Level 10

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
Astounding
PROC Star

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
Lapis Lazuli | Level 10 FK1
Lapis Lazuli | Level 10
Thanks Astounding! That's it! You are astounding 🙂
FK1
Lapis Lazuli | Level 10 FK1
Lapis Lazuli | Level 10

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?

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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