X command not working in sas 9.4

[Minaxi_Patel]

Hi,

I am able to run below query in sas 9.1 without any error but now it migrated to sas 9.4 and below script is not working there. In this script I am trying to trigger sql query through SQL Plus. While running same query in sas 9.4 it open commant promt for SQL plus but didn't trigger further query

x "%str(sqlplus &sql_signon @%"&filepath.&sas.10 - SASCode\SQLPlus\ray_calc_&context_id._&process_id._&server_id..sql%")";

Any functions required to add in the query. Even I tried %sysexec but same issue

[Tom]

You probably have different default options sets.  Check the XWAIT option in particular.

[Minaxi_Patel]

Thanks Tom
it seems 'Sql_signon' command is not recognize in command promp

[ballardw]

If you run this code:

%put &sql_signon;

what kind of result do you get?

You may be using a macro variable created by some process in your 9.1 install that has not been migrated over to your 9.4 version.

Also when you upgraded did you by any chance move to a server version from a stand alone SAS install? If so then the command is being sent to the server and it's permissions and even installed programs may well vary.

If you get warnings or errors it would help to post them to help diagnose the issue.

[Minaxi_Patel]

Hi,

Thanks for reply. Yes you are right. it seems some setting need to update. but not sure where we can update it. Normally we can run Script in SQL plus but not through sas. I am getting below error. tried so do settings for ORACLE_HOME  but still same issue. I have checked macro variables give correct values as we creat macro variables in same code.

Stderr output:

Error 6 initializing SQL*Plus

SP2-0667: Message file sp1<lang>.msb not found

SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

NOTE: DATA statement used (Total process time):

      real time           0.32 seconds

      cpu time            0.01 seconds

[Tom · Accepted solution]

To debug calls to operating system commands like to use a data step with PIPE on infile.  That way you can read the output of the command, including any error messages.

data _null_;
  infile  %sysfunc(quote(sqlplus &sql_signon 
@"&filepath.&sas.10 -SASCode\SQLPlus\ray_calc_&context_id._&process_id._&server_id..sql"
  )) pipe ;
  input ;
  put _infile_;
run;