BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Minaxi_Patel
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

5 REPLIES 5
Tom
Super User Tom
Super User

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

Minaxi_Patel
Calcite | Level 5

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

ballardw
Super User

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
Calcite | Level 5

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
Super User Tom
Super User

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;

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
  • 5 replies
  • 3646 views
  • 0 likes
  • 3 in conversation