DATA Step, Macro, Functions and more

X command not working in sas 9.4

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

X command not working in sas 9.4

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


Accepted Solutions
Solution
‎03-28-2017 07:39 AM
Super User
Super User
Posts: 6,498

Re: X command not working in sas 9.4

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;

View solution in original post


All Replies
Super User
Super User
Posts: 6,498

Re: X command not working in sas 9.4

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

Occasional Contributor
Posts: 7

Re: X command not working in sas 9.4

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

Super User
Posts: 10,483

Re: X command not working in sas 9.4

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.

Occasional Contributor
Posts: 7

Re: X command not working in sas 9.4

[ Edited ]

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

Solution
‎03-28-2017 07:39 AM
Super User
Super User
Posts: 6,498

Re: X command not working in sas 9.4

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;
☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 254 views
  • 0 likes
  • 3 in conversation