Help using Base SAS procedures

PROC SQL Execute Stored Procedure Warnings

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

PROC SQL Execute Stored Procedure Warnings

Hi,

 

I'm executing a MS SQL Stored Procedure from SAS DI Studio like this:

PROC SQL ;
  CONNECT TO odbc (&_OUTPUT1_connect.) ;
  EXECUTE (TheStoredProcedure) BY odbc ;
  DISCONNECT FROM odbc ;
QUIT ;

However the Stored Procedure contains print messages like:

PRINT 'Rows inserted: ' + CAST(@rowsInsertedHD AS VARCHAR(100))

And those messages are come back as warnings in SAS.

 

Is there any way to suppress those warnings, without altering the Stored Procedure?

 


Accepted Solutions
Solution
‎06-01-2018 03:06 AM
Occasional Contributor
Posts: 11

Re: PROC SQL Execute Stored Procedure Warnings

I've made a workarround by creating a SQL job that executes the SP. From SAS I execute the job. No warnings then and good enough for now.

We can't modify the Stored Procedure indeed.

Thanks for your help!

View solution in original post


All Replies
Super User
Posts: 3,926

Re: PROC SQL Execute Stored Procedure Warnings

[ Edited ]
Posted in reply to ThierryHerrie

What happens if you use: proc sql nowarn;

 

Note to stored procedure developer: Any printed output should be optional and turned off by default - program design 101.

Occasional Contributor
Posts: 11

Re: PROC SQL Execute Stored Procedure Warnings

Still getting warnings

Super User
Posts: 3,926

Re: PROC SQL Execute Stored Procedure Warnings

Posted in reply to ThierryHerrie

What's the issue for you? The warnings triggering a non-zero return code on the job or something else?

Occasional Contributor
Posts: 11

Re: PROC SQL Execute Stored Procedure Warnings

Yes, that's basically it.
Also because I am surprised that it looks like SAS treats the print statement from SQL as warnings
Super User
Posts: 3,926

Re: PROC SQL Execute Stored Procedure Warnings

Posted in reply to ThierryHerrie

What scheduler do you use? You might be able to tweak the batch script file to ignore warnings in this case. You could also open a track with SAS Tech Support to see if they have any other ideas.

 

I'm assuming here there is no possibility of getting the stored procedure changed as that would be the best option.

Solution
‎06-01-2018 03:06 AM
Occasional Contributor
Posts: 11

Re: PROC SQL Execute Stored Procedure Warnings

I've made a workarround by creating a SQL job that executes the SP. From SAS I execute the job. No warnings then and good enough for now.

We can't modify the Stored Procedure indeed.

Thanks for your help!
Super User
Posts: 3,926

Re: PROC SQL Execute Stored Procedure Warnings

Posted in reply to ThierryHerrie

@ThierryHerrie - No problem! Please mark your solution as answered in that case.

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 383 views
  • 5 likes
  • 2 in conversation