cant figure out basic sas sql delect cascade.

thryce85 — Tue, 11 Jan 2022 17:09:40 GMT

Hows it going? noob sas sql question here . Im trying to learn foreign references and what Im trying to do is establish a primary foreign relationship and then delete from the primary and have it auto delete in the table with an associated foreign key . Say I have stopped selling cars > 30000 because no one is buying them. I want to delete all the cars in my records that match this condition from all linked tables by simply deleting the entries in the table with the primary key. I thought that using DELETE CASCADE would accomplish this but I must be missing sth. It says this is not supported or maybe my syntax is wrong? Any help would be appreciated.

%macro make_sql;

proc sql ;
create table k1 (id num , model char , msrp num ) ;
insert into k1
select monotonic()*2 as id , model , msrp from sashelp.cars;

quit;

proc sql;
alter table k1
add constraint prime primary key(id);
run;

proc sql ;
create table k2 (id num , model char, inventory num );
insert into k2
select monotonic()*2 ,model, invoice
from sashelp.cars;
quit;

proc sql ;
alter table k2
add constraint foreign2 foreign key(id)
references k1
on delete cascade ;
quit;

%mend make_sql;

%macro del_sql;
proc sql;
alter table k2
drop foreign key foreign2;

drop table k1;
quit;

proc sql;
drop table k1;
quit;
%mend del_sql;

%make_sql;

* condition to remove the primary values
proc sql;
delete from k1
where msrp > 30000
;
quit;

*K1 deletes the values k2 stays the same? ;

proc contents data=k1 ;
run;

proc contents data=k2;
run;

*********************************************************

Ive also replaced ON DELETE CASCADE with ON UPDATE CASCADE

then used below. its not working either so I must be missing sth very basic. 😞

proc sql;
update k1
set id = 0
where msrp > 30000
;
quit;

Re: cant figure out basic sas sql delect cascade.

SASKiwi — Tue, 11 Jan 2022 19:45:02 GMT

Please post your SAS log. It looks like you are using syntax which isn't supported in SAS SQL and the errors in the SAS log should point to these.

Not sure why you are experimenting with table keys and constraints. Please bear in mind that SAS data libraries are not RDBMSs like Oracle or SQL Server. There is no need to define table schemas before populating the tables - just populate the table and the table "schema" will take care of itself.

topic cant figure out basic sas sql delect cascade. in SAS Programming

cant figure out basic sas sql delect cascade.

Re: cant figure out basic sas sql delect cascade.