topic Constraints Removed in SAS Programming

Constraints Removed

Ujjawal — Sun, 05 Jun 2016 20:36:17 GMT

I have added constraints into a table but it removes it when someone overwrites the table. Is there any way to prevent it?

DATA TEMP;
SET SASHELP.CLASS;
RUN;

PROC SQL;
ALTER TABLE TEMP
ADD CONSTRAINT NOT_NULL_WEIGHT NOT NULL(WEIGHT);
QUIT;

DATA TEMP;
SET TEMP;
IF AGE >=15 THEN WEIGHT = .;
RUN;

Re: Constraints Removed

PGStats — Sun, 05 Jun 2016 21:21:48 GMT

Constraints are kept and respected if you stay within SQL. Try

proc sql;
update temp
set weight = .
where age >= 15;
quit;

Re: Constraints Removed

PGStats — Mon, 06 Jun 2016 02:29:00 GMT

Use MODIFY if you want to retain the constraint. But note that

DATA TEMP;
modify TEMP;
IF AGE >=15 THEN WEIGHT = .;
RUN;

is not equivalent to the SQL UPDATE query above. There is no rollback with MODIFY.

Re: Constraints Removed

Ksharp — Mon, 06 Jun 2016 02:49:01 GMT

Add a password in this table to avoid to rewrite it .

DATA TEMP(pw=aw12);
SET SASHELP.CLASS;
RUN;

data temp;
 set sashelp.class;
run;