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

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

1 ACCEPTED SOLUTION

Accepted Solutions
loobin
Calcite | Level 5

     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

6 REPLIES 6
SASKiwi
PROC Star

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.

loobin
Calcite | Level 5

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

Tom
Super User Tom
Super User

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;

Ksharp
Super User

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

Tom
Super User Tom
Super User

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;

loobin
Calcite | Level 5

     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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 3097 views
  • 6 likes
  • 4 in conversation