topic Insert rows based on matching values in SAS Programming

Insert rows based on matching values

David_Billa — Wed, 22 Apr 2020 04:31:53 GMT

I'm trying to insert rows in base table (WANT) based on the matching values 'run_id', but it's throwing error if I try with the code below.

Am I missing something in the where clause? I want to insert rows in base table only run_id matches between base table (WANT) and &tab.

proc sql undo_policy=none;
Insert into IFR.WANT (table_name,table_count) 
select table_name,table_count from &tab where run_id in (select run_id from &tab);
quit;

WARNING: The SQL option UNDO_POLICY=REQUIRED is not in effect. If an error is detected when processing this INSERT statement, that 
         error will not cause the entire statement to fail.
ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Cannot insert the value NULL into column 
       'RUN_ID', table 'IFR.WANT'; column does not allow nulls. INSERT fails. : [SAS][ODBC SQL Server Wire 
       Protocol driver][Microsoft SQL Server]The statement has been terminated.
NOTE: 1 row was inserted into IFR.WANT-- of these 1 row was rejected as an ERROR, leaving 0 rows that were inserted 
      successfully.

Re: Insert rows based on matching values

PGStats — Wed, 22 Apr 2020 04:42:12 GMT

I think you either have to supply a value for run_id in the insert instruction or define run_id in the receiving table to automatically generate an id (type=autonumber in some databases)

Re: Insert rows based on matching values

Kurt_Bremser — Wed, 22 Apr 2020 07:48:59 GMT

It's a case of Maxim 3: Know Your Data. This also means to know the integrity rules set up for a target table in a remote database, which then gives you the information you need to prepare your update table correctly.