Date:         Fri, 15 Nov 2002 15:39:19 GMT
Reply-To:     "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Organization: EarthLink Inc. -- http://www.EarthLink.net
Subject:      Re: INSERT Set Column
Content-Type: text/plain;

"chuaby" <chuaby@hotmail.com> wrote in message
news:a38d999f.0211150326.7479d9b1@posting.google.com...
> Hi
>
> I have an audit trail table with 20 columns.
>
> I need to create a record that only update 3 fields. May i know how
> can i do that ?
>
> I am trying to do something like (if the statement permits):
>
> proc sql;
> Insert into Trail
> set Error_Msg = 'Missing Parameter',
>     (CUSTID,TestID) = (select Distinct CUSTID,TESTID from MISSING)
> run;
>
> instead of :
>
> proc sql;
> Insert into Trail
> select '',''.....'', 'Missing Parameter',CUSTID,TestID
> from MISSING;
> run;
>
> Reason being the Trail stucture keeps changing and i do not wish to
> change the SQL statement everytime when there is a change.
>
> Thanks
> Boon Yiang

Use INSERT form that names the columns that will receive the selected
columns.
See the last insert in the following sample:

proc sql;
  create table for_wavy
    (col_3 char(20), col_1 char(20), col_2 char(20));

  create table wavy
    (col_1 char(20), col_2 char(20), col_3 char(20));

  insert into wavy values ('r1c1', 'r1c2', 'r1c3');
  insert into wavy (col_3) values ('r2c3');

  insert into for_wavy
    values ('col_3', 'col_1', 'col_2')
    values ('column_3', 'column_1', 'column_2')
  ;

  insert into wavy (col_1, col_2, col_3)
  select distinct 'fixed value' as col_1, col_2, col_3 from for_wavy;
quit;

--
Richard A. DeVenezia
http://www.devenezia.com/downloads/sas/macros