topic Re: bypass foreign key in proc step sas in SAS Programming

bypass foreign key in proc step sas

Liliya95 — Fri, 10 Aug 2018 06:52:42 GMT

It is possible to skip foreign keys when requesting.
There is a table in which you want to write data. This table has a primary and foreign keys. The table entry is made from a temporary table. but an error occurs because there are no foreign keys in the temporary table. How can I solve this problem?

  CREATE TABLE "MAIN"."TABLE_FOR_TEST2" 
   (	"ONE" VARCHAR2(20 BYTE), 
	"TWO" VARCHAR2(20 BYTE), 
	 CONSTRAINT "PRIMARY" PRIMARY KEY ("TWO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "U"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "U" ;

%macro test;
%macro dummy; %mend dummy;
data test(keep=ONE TWO);
		length ONE $20 TWO $20;
		ONE="wdq";
		TWO="dadf";
run;


proc sql noprint;
							create table test2(ONE char(20), TWO char(20), constraint prim primary key(TWO)); 							insert into test2 select * from test;
quit;

proc append base MAIN.TABLE_FOR_TEST2  data=test2 nowarn force ; quit;

Re: bypass foreign key in proc step sas

SASKiwi — Fri, 10 Aug 2018 07:06:35 GMT

Why do you need foreign keys? It would be better to upload your SAS data to a staging table in the database first which doesn't have constraints or indexes. Then load from the staging table using passthru SQL designed to handle constraints.

Re: bypass foreign key in proc step sas

LinusH — Fri, 10 Aug 2018 07:08:37 GMT

Not sure if I follow you here.
FK in a target tables doesn't relate to keys in a source table. It relates to a PK in another (hopefully) permanent table.

Re: bypass foreign key in proc step sas

Kurt_Bremser — Fri, 10 Aug 2018 07:09:56 GMT

When a target table has constraints set, new data added to the table has to meet those constraints. That is the purpose of constraints and integrity rules.

I have never seen the necessity to have integrity rules on data warehouse tables. In fact, they would only get in the way.

If this is not a production database table, have the constraints/IR removed. Otherwise you have to prepare your data so that it meets the IR

Re: bypass foreign key in proc step sas

Liliya95 — Fri, 10 Aug 2018 07:57:31 GMT

Foreign key is specified in the table when it is created. and I can not delete it. but the data in the table must be written. I try use insert, but have error

Re: bypass foreign key in proc step sas

Kurt_Bremser — Fri, 10 Aug 2018 08:07:13 GMT

Your data has to meet the integrity constraints defined for that table. Consult your DBA people, they should be able to help you make your data compatible.