My SQL Server table has a few default contraints:
[Name] [varchar](8) NOT NULL, [Sex] [char](1) NOT NULL CONSTRAINT [DF_TARGET_SEX] DEFAULT ('U'), [Age] [int] NOT NULL CONSTRAINT [DF_TARGET_AGE] DEFAULT ((-1)), [Height] [numeric](7, 1) NOT NULL CONSTRAINT [DF_TARGET_HEIGHT] DEFAULT ((-1)), [Weight] [numeric](7, 1) NOT NULL CONSTRAINT [DF_TARGET_WEIGHT] DEFAULT ((-1)), [valid_from] [datetime2](0) NOT NULL CONSTRAINT [DF_TARGET_VALID_FROM] DEFAULT (getdate()), [valid_to] [datetime2](0) NOT NULL CONSTRAINT [DF_TARGET_VALID_TO] DEFAULT ('9999-12-31 23:59:50'), [current_record] [char](1) NULL CONSTRAINT [DF_TARGET_CURRENT_RECORD] DEFAULT ('Y'), [DimensionCheckSum] [int] NOT NULL CONSTRAINT [DF_TARGET_DimensionCheckSum] DEFAULT ((-1)), [LastUpdated] [datetime] NOT NULL CONSTRAINT [DF_TARGET_LastUpdated] DEFAULT (getdate()), [UpdatedBy] [varchar](50) NOT NULL CONSTRAINT [DF_TARGET_UpdatedBy] DEFAULT (SUSER_SNAME()),
If I submit this in SSMS, all the above fields are auto-populated, and get their default values:
INSERT INTO tmp.target (Name) VALUES ('A')
When I have an ODBC connection to this table in SAS, and I execute the below code:
data foo; if 0 then set tmp.target (keep=name); Name='B'; output; stop; run; proc append base=tmp.target data=foo force nowarn; run;
All the default values are auto-populated, except for UpdatedBy and Sex.
If I use explicit pass-through it works as expected:
proc sql; connect using tmp; execute( INSERT INTO tmp.target (Name) VALUES ('C') ) by tmp; quit;
My ODBC libref is allocated as follows:
%macro allocate_sqlsvr_database(libref=,database=,schema=dbo); %* set connection string ; %let connect = NOPROMPT="Driver={SQL Server Native Client 10.0};Server=XXXXX,#####;Database=&database;Trusted_Connection=yes;"; %* set any libname options ; %let options = bulkload=yes schema=&schema reread_exposure=yes DBINDEX=YES UPDATE_LOCK_TYPE= NOLOCK; %* issue the libname statement ; libname &libref odbc &connect &options; %mend; %allocate_sqlsvr_database(libref=TMP, database=MyDatabase,schema=tmp)
Any ideas why these two default values aren't populated when using PROC APPEND? Esp. UpdatedBy? Could this be a bug?
SAS 9.3 on Windows...
Thanks...
There is the th BL_KEEPNULLS options that seems to be tailored for this problem. Unfortunately it seems only to be supported for OLEDB connections.
For ODBC you could try setting the IGNORE_READ_ONLY_COLUMNS to YES, if I recall right I think we solved this problem by doing that.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.