CODEWITHSUNDEEP
sqlsas
derbestederbesten
derbestederbesten

Reputation: 75

Cannot reopen error when updating table in SAS with SQL

I have 2 tables:

I want to set t1.A to value t2.N where t1.A = t2.A and t1.B = T2.B.

It sounds simple, but I have no idea to solve this; I tried something like:

update t1 set A = (select t2.N 
                     from t1,t2 
                    where t1.A = t2.A 
                      and t1.B = t2.B)
......

But this gives an error:

ERROR: You cannot reopen t1.DATA for update access with member-level control because t1.DATA .....

Any ideas?

Upvotes: 1

Views: 1356

Answers (3)

w. Patrick Gale
w. Patrick Gale

Reputation: 2307

Make a copy of t1 (we will call it t1_copy for example) and use the copy in your select statement. The reason for the table copy is you cannot select from a table you are updating.

%_eg_conditional_dropds(t1_copy); /* drop the copy before using it (if one exists) */
PROC SQL;
    CREATE TABLE t1_copy AS
        SELECT * FROM t1;

   update t1 set A = (select t2.N 
                     from t1_copy,t2 
                    where t1_copy.A = t2.A 
                      and t1_copy.B = t2.B)
QUIT;

Upvotes: 0

kstats9pt3
kstats9pt3

Reputation: 873

A side note besides the syntax error: you're getting that ERROR: You cannot open WORK.T1.DATA for output access with member-level control because WORK.T1.DATA is in use by you in resource environment DATASTEP. because your output dataset is still open in the window. Close the VIEWTABLE:Work.T1 prior to re-running.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I suspect you just want a correlated subquery:

update t1
    set A = (select t2.N 
             from t2 
             where t1.A = t2.A and t1.B = t2.B
            );

Note: You should be careful that the subquery returns only one row.

Upvotes: 1

Related Questions