What command can I use to create a column in a SAS table and initialize it to default value 'N' ?
Example:
proc sql;
alter table LIB1.TABLE1
ADD FLAG char(1) format = $1. DEFAULT 'N'
;
I couldn't find a way to add a true default constraint (i.e. one that creates a value on insertion). Looks like you'll need to insert a value or it will be missing unless you run the update statement after each insert.
proc sql;
alter table LIB1.TABLE1
ADD FLAG char format = $1.;
update LIB1.TABLE1
set FLAG='N';
quit;
Just to add, as that is a flag you may run into issues if you default a value in rather than conditionally. I always have a three part case, Y if true, N if not true, else value (could be default or value). Otherwise defaulting in N might indicate something is not true, when missing for instance. E.g.
set FLAG=case when exists(select distinct A from B where A=5) then "Y"
when exists(select distinct A from B where A=4) then "N"
else "" end
If you default then A=1,2,3 would also give N, which could be misleading i.e. indicates they have a record=4.
I couldn't find a way to add a true default constraint (i.e. one that creates a value on insertion). Looks like you'll need to insert a value or it will be missing unless you run the update statement after each insert.
As https://communities.sas.com/people/DBailey mentions, there is no support i SAS for default constraints.
What you can do is to use CHECK or NOT NULL constraints to enforce the inserting process' to use (correct) values.
How will this/these tables be maintained?
If this is critical, and the value is hard to update/insert afterwards, consider using an external data source that supports default constraints.
the only default that SAS defaults is missing.
If you insist on seeing N then just apply a user format that supplies an N for missing.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.