topic Re: X command not working in sas 9.4 in SAS Programming

X command not working in sas 9.4

Minaxi_Patel — Fri, 24 Mar 2017 13:07:27 GMT

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

Re: X command not working in sas 9.4

Tom — Fri, 24 Mar 2017 13:11:48 GMT

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

Re: X command not working in sas 9.4

Minaxi_Patel — Fri, 24 Mar 2017 14:22:52 GMT

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

Re: X command not working in sas 9.4

ballardw — Fri, 24 Mar 2017 14:38:07 GMT

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.

Re: X command not working in sas 9.4

Tom — Fri, 24 Mar 2017 14:38:18 GMT

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;

Re: X command not working in sas 9.4

Minaxi_Patel — Mon, 27 Mar 2017 14:43:13 GMT

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