topic Re: PROC SQL - Update table using another row from the same table in SAS Programming

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

gamotte — Thu, 17 Nov 2016 16:12:55 GMT

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 !

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

RW9 — Thu, 17 Nov 2016 16:35:47 GMT

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.

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

gamotte — Thu, 17 Nov 2016 16:45:07 GMT

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.

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

Ksharp — Fri, 18 Nov 2016 08:53:37 GMT

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;

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

gamotte — Fri, 18 Nov 2016 09:25:01 GMT

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