SAS SQL : ERROR and DEBUG

[LineMoon]

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.

[LinusH]

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.

[BrunoMueller]

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;

[LinusH]

Great tip !

[jakarman]

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#n0732u1mr57ycrn1ur... 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#p0te4txy6dyzs9n1cspt...

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.