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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.