topic SAS SQL : ERROR and DEBUG in SAS Programming

SAS SQL : ERROR and DEBUG

LineMoon — Thu, 28 May 2015 20:25:58 GMT

I run this good,

I get the error message: ERROR: Observation was not added/updated because a matching primary key value was not found for foreign key var1.

Please, do you have any solution ?

Is it possible to debug a sas sql proc ?

Thank you

proc sql;

insert into lb.test1 * from test2;

quit;

ERROR: Observation was not added/updated because a matching primary key value was not found for foreign key var1.

NOTE: Deleting the successful inserts before error noted above to restore table to a consistent state.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

Re: SAS SQL : ERROR and DEBUG

LinusH — Fri, 29 May 2015 08:33:59 GMT

I don't think that there is built-in function to trace which records get's rejected due to a constraint violation.

But it's easy to check, just query the table which holds the PK to find out which FKs are rejected.

Re: SAS SQL : ERROR and DEBUG

BrunoMueller — Fri, 29 May 2015 11:28:53 GMT

You could switch on the audit trail functionality on the SAS data Set, this will then give you more detailed information on why a insert did not work. You also can get the before and after image, if you make changes to existing observations.

Here is a sample code that illustrates the use of the audit trail.

*
* create some test data
*;
data test;
  do i = 1 to 10; 
    char = put(i, roman.);
    output; 
  end; 
run; 

*
* add a primary key to the table
*;
proc sql; 
  alter table test
    add primary key ( i )
  ;
quit; 

*
* switch on audit trail
*;
proc datasets lib=work;
  audit test;
  initiate  audit_all=yes;
run; 

quit; 

*
* test data to inserts
*;
data test2;
  do i = 7 to 12; 
    char = put(i, z4.);
    output; 
  end; 
run; 

*
* add data to existing table
* undo_policy will continue processing even
* if there is a problem
*;
proc sql undo_policy=none;
  insert into test select * from test2;
quit; 

*
* print the audit trail information
*;
title "Audit Trail"; 
proc print data=test(type=audit);
run; 
title;

Re: SAS SQL : ERROR and DEBUG

LinusH — Fri, 29 May 2015 11:45:36 GMT

Great tip !

Re: SAS SQL : ERROR and DEBUG

jakarman — Fri, 29 May 2015 11:46:20 GMT

As simple and easy your question is as difficult it is. Possible to debugging a proc sql proc?

1/ Reverse engineering of “proc SQL” is what WPS has done.
I assume this is not your question but you are thinking how the SQL handling internally is solved. The dataprocessing.
The whole goal of using SQL is to hide common known solutions as sorting, indexing and balance line from the user.

Knowing that SQL the data handling will do with those you are knowing it is better than the average programmer is able to do with far less human effort. The top programmers can do things that will never come into reach of SQL. That is why you have NOSQL HAdoop and more of those.

2/ “proc SQL”is SAS internal and external processing.

2a/ Extermal you have sastrace debugging
http://support.sas.com/documentation/cdl/en/acreldb/67589/HTML/default/viewer.htm#n0732u1mr57ycrn1urf24gzo38sc.htm an external DBMS often is supporting the “explain” or “plan” statement http://docs.oracle.com/cd/B10500_01/server.920/a96533/ex_plan.htm https://technet.microsoft.com/en-us/library/ms178071(v=sql.105).aspx There is a whole area behind there how a DBA can change the behavior (plans) and nothing is changed in the SQL.

2a/ Internal SAS Sql processing is more limited.

It is there but tedious to work on. http://www2.sas.com/proceedings/sugi30/101-30.pdf and http://support.sas.com/resources/papers/proceedings13/200P-2013.pdf
http://www8.sas.com/scholars/Proceedings/2006/ETL/ET07_06.PDF Having those papers you have your answer. It is possible but not easy (_method)

3/ The logical processing error and more.
This is where your question is about. SAS datasets normally are seen just as being sequential ones. However you can add keys and constraints in a way the other SQL environments are knowing those. SQL was designed at the transactional era. SAS is supporting SQL-99, others mostly SQL-2013.
What is happening with those that they are adding additional logic to the data processing. That logic can be defined by yourself or is part of something somebody else has done. http://support.sas.com/documentation/cdl/en/lrcon/67885/HTML/default/viewer.htm#p0te4txy6dyzs9n1csptlqoktu7m.htm

Now see sheet 36 page 39 of https://support.sas.com/training/tutorial/el/libsppg3_cic.pdf there is your error message and the explanation

I think you have now all material from a detailed top-view to solve your SQL questions.