Date:         Sun, 25 Jan 1998 12:26:17 -0500
Reply-To:     HERMANS1 <hermans1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         HERMANS1 <hermans1@WESTAT.COM>
Subject:      Re: SQL UPDATE question
Comments: To: Jason Jones <jasonj@RCF.USC.EDU>

I believe that you are asking the same type of question that I
answered in an earlier posting.  See below.  You would (almost) never
use a three-level name in SAS.  The LIBNAME refers to a library
(directory path) where SAS can find the DATASET.  An ALIAS (denoted by
<table> AS <alias> in a FROM clause) indicates the source of a column.
In the SAS SQL solution below, a nested subquery (A.K.A. an "in-line
view") uses aliases to distinguish the sources of columns with the
same name.   Sig
 
______________________ Earlier Reply _________________________________
 
 
Try a correlated subquery as shown in this program:
/* Test datasets */
data employee;
  input @1 ID $char3. @6 Bencode $1. ;
cards;
001  x
003  x
002  x
005  x
004  x
;
run;
 
data benefits;
  input @1 ID $char3. @6 Bencode $1. ;
cards;
002  y
004  y
001  y
005  y
003  y
;
run;
 
   proc sql;
     update employee as t1
     set bencode=(select t2.bencode
                     from benefits as t2
                     where t1.ID=t2.ID
                 )
     ;
   quit;
proc print;
run;
 
As you might expect, the benefits table will have to have a distinct,
primary ID that links back to the employee table.   Sig
 
_______________________ Original Message ____________________________
Subject: Help: Proc SQL
Author:  Parry JohnBaptiste <pjb@CYBERRAMP.NET> at Internet-E-Mail
Date:    1/5/98 7:33 PM
 
 
I am a SAS neophyte and the following simple SQL code works in other
relational languages but not in PROC SQL. Can anyone help?
 
Proc SQL;
Update Employee
Set  Employee.Bencode = Benefits.Bencode
From Employee, Benefits
Where Employee.SSN = Benefits.SSN;
 
 
 
___________________________ New Question _________________________________
Subject: SQL UPDATE question
Author:  Jason Jones <jasonj@RCF.USC.EDU> at Internet-E-Mail
Date:    1/24/98 3:42 PM
 
 
Hi all,
        I'm trying to use information in one table to update information
in another table with the UPDATE statement in PROC SQL.  All the examples
I've found only update information in one column with another column in
the same table.  Neither the SET nor the WHERE clauses like having a three
level name (e.g., libname.dataset.column).  I feel certain there must be a
way to do this, but I can't find it.
 
                        Jason
 
P.S. Yes, I do know how to do this with the DATA step, but I want to do it
with PROC SQL.
 
Jason Jones
J2DbStat
Data Management/Analysis
(213) 508-5347