PROC SQL - Update table using another row from the same table

[gamotte]

Hi,

I would like to update the following sample dataset

data test;
infile datalines dlm=',';
input id cat $ status $;
datalines;
1,A,KO,
2,A,KO,
3,A,KO,
2,B,NR
;
run;

The rule is to set the status of a category A (cat="A") individual to "OK" if he also belongs to the category B.

With the above example, the second row would become :

2,A,OK

I thought of using a proc sql as follows :

proc sql;
	UPDATE test a
	SET status="OK"
	WHERE a.cat="A" and a.id in (
		SELECT b.id FROM test b
		WHERE b.cat="B"
	);
quit;

But this generates an error :

ERROR: You cannot reopen WORK.TEST.DATA for update access with member-level control because
WORK.TEST.DATA is in use by you in resource environment SQL.

I could, in a first step, create a second dataset with the ids of all B individuals but i try, when i can, to

avoid intermediary steps that distract the reader from the main program goal.

Is the lock on the dataset a limitation of SAS or is it possible to refer the same dataset in the SET

and the WHERE clauses of an UPDATE ?

Thanks !

[RW9 · Accepted solution]

No, you can't sub-qeury a dataset which is open for update.  It could create a cycle which never ends.  What is the purpose of the code.  Simpest way to make your code work, duplciate the dataset:

data test test2;
  infile datalines dlm=',';
  input id cat $ status $;
datalines;
1,A,KO,
2,A,KO,
3,A,KO,
2,B,NR
;
run;

proc sql;
  update TEST A
  set STATUS="OK"
  where A.CAT="A" 
  and A.ID in (select distinct ID from TEST2 where CAT="B");
quit;

 There are however other ways of doing it, datastep, sorting etc.

[gamotte]

Hi,

It seems to me that this kind of things is possible under other flavors of SQL (the subquey being evaluated first,

there is no cycle), that is why i asked the question.

I will thus duplicate the dataset as you advised me.

Thanks.

[Ksharp]

you can change option to do it. But it is risky.



data test;
  infile datalines dlm=',';
  input id cat $ status $;
datalines;
1,A,KO,
2,A,KO,
3,A,KO,
2,B,NR
;
run;
proc sql undo_policy=none nowarn;
	UPDATE test a
	SET status="OK"
	WHERE a.cat="A" and
    exists(select * from test where id=a.id and cat='B')
;
quit;

[gamotte]

Thank you for the tip. I will avoid to use such options as, like you say, it can be risky.