Help using Base SAS procedures

Proc SQL: User Defined Error & Messages to SAS log

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Proc SQL: User Defined Error & Messages to SAS log

Hey All,

     I am throwing this message in a bottle out there.

     My beef is this:

     In a data step, I find the ERROR statement a very useful feature (http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000289394.htm). Below is how I've used it.

data

AL_base E2_base GM_base BL_base E3_base;

     set xml.Raw_roster_20DEC2011;

     if      course='AL' then output AL_base;

     else if course='E2' then output E2_base;

     else if course='GM' then output GM_base;

     else if course='BL' then output BL_base;

     else if course='E3' then output E3_base;

     else error 'Unanticipated course in file -- ' course=;

run;

  I would like to perform a similar action in a Proc SQL step something like:

proc sql;

select case

when x<0 then error 'Error message to SAS log'

else x end as new

from work.binary;

quit;

     Am I making sense? Any suggestions?

Thanks,

Huey


Accepted Solutions
Solution
‎02-25-2012 10:16 PM
New Contributor
Posts: 3

Re: Proc SQL: User Defined Error & Messages to SAS log

     Thank you Tom, KSharp, and Kiwi for your thoughts and effort. People like yourselves should not be taken for granted as you are the very reason why these discussion forums function, and function well at that.

     Tom, I think your solution is the best way to emulate the behavior of the ERROR statement, as we've presented it in this context; a very clever solution, and uniquely useful. However, the SAS 'intended' functionality, within PROC SQL, that I've found that most closely approaches the ERROR statement is an INTEGRITY CONSTRAINT. Below is an approximation of what you accomplished in your reply, in that it raises an alarm for a 'Sex' value not equal to 'F'. The difference here is: 1) The step stops at the first occurance. Failing the step entirely. 2) How many times the violation occurs is not recorded.

proc sql;

      /*Create an empty copy of the table to read in.*/

      create table x like sashelp.class;

     /*Add integrity constraint and corresponding error message.*/

     alter table x

          add constraint  Check_Sex check(Sex eq 'F')

                message="The field 'Sex' had value other than 'F'."

                msgtype=NEWLINE;

      /*Fill the empty dataset with data. Watch the step

        fail as the integrity constraint is violated.*/

     insert into x

            select * from sashelp.class;

quit;

     As for Kiwi's initial response, I maintain this is ultimately the right way of looking at my 'problem'. That is, find the right tool for the job instead of complaining about the tool initially chosen. This post is just a manifestation of my stubborness.

Thanks,

Huey

View solution in original post


All Replies
Super User
Posts: 3,102

Proc SQL: User Defined Error & Messages to SAS log

I am curious to know why, when you have a workable solution in a DATA step, why you want to convert it to SQL? Your DATA step example writes out multiple tables, and this is not possible in a single SQL query.

Please bear in mind that SQL is primarily a query and data manipulation language that has more limited functionality, particularly in the area of error checking and messaging you are interested in.

I have built examples of the sort of functionality you are after in non-SAS SQL (eg Oracle, Sybase etc) but it involves use of SQL cursors and stored procedures for which there is no equivalent functionality in SAS SQL.

If SQL does not give you any major advantages, why not use a DATA step. Or if you do need to use SQL, add an error-checking DATA step afterwards.

New Contributor
Posts: 3

Proc SQL: User Defined Error & Messages to SAS log

Kiwi,

     Thanks for taking the time to think and respond to my issue. I think you are completely right that I should think about what advantages an SQL step has that are worth loosing functionality that the data step provides. Wise response. My main motivation behind this post was a stubborn will to figure out what I think ought to be possible.

Thanks again,

Huey

Super User
Super User
Posts: 6,498

Re: Proc SQL: User Defined Error & Messages to SAS log

Try using RESOLVE function with a %PUT.

proc sql ;

  create table want as

    select case when (sex='F') then sex||resolve('%put Error: Sex='||sex)

           else sex end as sex

         , name

    from sashelp.class

  ;

quit;

proc print data=want;

run;

Super User
Posts: 9,671

Re: Proc SQL: User Defined Error & Messages to SAS log

Tom.

But you do not set automatic variable _ERROR_ =1.

According to documentation, in addition to putting message into log , also need to set _ERROR_ equal 1.

Ksharp

Super User
Super User
Posts: 6,498

Re: Proc SQL: User Defined Error & Messages to SAS log

As far as I know the automatic variable _ERROR_ is a data step concept.

But you could also use the resolve to set a macro variable that could be tested after the step.

Or even to count the errors.

proc sql ;

%let errors=0;

  create table x as

   select *

        , case when (sex='F') then '0'

               else '1'||resolve('%let errors=%eval(&errors+1);')

               end as e

   from sashelp.class

  ;

quit;

%put errors=&errors;

Solution
‎02-25-2012 10:16 PM
New Contributor
Posts: 3

Re: Proc SQL: User Defined Error & Messages to SAS log

     Thank you Tom, KSharp, and Kiwi for your thoughts and effort. People like yourselves should not be taken for granted as you are the very reason why these discussion forums function, and function well at that.

     Tom, I think your solution is the best way to emulate the behavior of the ERROR statement, as we've presented it in this context; a very clever solution, and uniquely useful. However, the SAS 'intended' functionality, within PROC SQL, that I've found that most closely approaches the ERROR statement is an INTEGRITY CONSTRAINT. Below is an approximation of what you accomplished in your reply, in that it raises an alarm for a 'Sex' value not equal to 'F'. The difference here is: 1) The step stops at the first occurance. Failing the step entirely. 2) How many times the violation occurs is not recorded.

proc sql;

      /*Create an empty copy of the table to read in.*/

      create table x like sashelp.class;

     /*Add integrity constraint and corresponding error message.*/

     alter table x

          add constraint  Check_Sex check(Sex eq 'F')

                message="The field 'Sex' had value other than 'F'."

                msgtype=NEWLINE;

      /*Fill the empty dataset with data. Watch the step

        fail as the integrity constraint is violated.*/

     insert into x

            select * from sashelp.class;

quit;

     As for Kiwi's initial response, I maintain this is ultimately the right way of looking at my 'problem'. That is, find the right tool for the job instead of complaining about the tool initially chosen. This post is just a manifestation of my stubborness.

Thanks,

Huey

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 593 views
  • 6 likes
  • 4 in conversation