05-28-2015 04:25 PM
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 ?
insert into lb.test1 * from test2;
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.
05-29-2015 04:33 AM
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.
05-29-2015 07:28 AM
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.
05-29-2015 07:46 AM
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.