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
order.
 
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
looping macro?
 
Thanks once again.
 
Nelson
 
>> New version of SAS code <<
 
proc sql;
  /* create a macro variable holding all dataset names */
  select memname into :dsets separated by ' '
    from dictionary.members
    where libname eq 'DATA' and memtype eq 'DATA';
 
  /* create a macro variable holding number of datasets by using
     a system variable */
  %let numdsets=&sqlobs;
quit;
 
%macro decode;
 
  %local i dname;
 
  /* Loop around all datasets */
  %do i=1 to &numdsets;
 
      %let dname=%scan(&dsets,&i);
 
      %*** New Code ***;
      proc sql;
        update data.&dname a
        set decode = (select decode
                      from work.decode b
                      where a.code = b.code);
  %end;
 
%mend decode;