Date: Wed, 26 Jun 1996 09:16:15 +0100
Reply-To: Nelson Kinnersley <nelson@DIRCON.CO.UK>
Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From: Nelson Kinnersley <nelson@DIRCON.CO.UK>
Subject: Thank you for SQL column update replies
Thanks to Tim Berryhill, Paul Kent and Adam Hendrix for their responses to
my posting on updating a column using SQL.
The new SQL step inside the macro solves the RETAINing problem nicely. I
*was* aware why the retaining was happening but I was reluctant to DROP
(or RENAME and DROP) the original variable because it would alter the
order of the variables in the PDV. The new SQL approach preserves the
Using the system variable &SQLOBS saves some keystrokes and minimises
chance of errors if the previous WHERE clause was any more complicated.
Can the new code be improved so that a macro is not needed at all i.e. can
the first SQL be extended to work on the whole Library instead of using a
Thanks once again.
>> New version of SAS code <<
/* create a macro variable holding all dataset names */
select memname into :dsets separated by ' '
where libname eq 'DATA' and memtype eq 'DATA';
/* create a macro variable holding number of datasets by using
a system variable */
%local i dname;
/* Loop around all datasets */
%do i=1 to &numdsets;
%*** New Code ***;
update data.&dname a
set decode = (select decode
from work.decode b
where a.code = b.code);