Date: Wed, 19 Nov 2003 10:18:07 -0500
Reply-To: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject: Re: How to do table lookup in SAS when main dataset has multiple
records with the same key
Content-Type: text/plain
Susie:
In a simple test the Data step merge does what I would expect it to do. It
produces the same result as a SAS SQL inner join. I've also included an
example of a SQL left join to a look-up table. The query creates a view of
the data with the value attached:
/* Suppose I have my MAIN dataset (with fields: employee_id table_id),
and the lookup table in TABLE dataset (with fields: table_id value),
and I want to read the value from TABLE into MAIN for every employee:
*/
data main;
input employee_id table_id;
cards;
111 221
111 221
111 221
222 322
333 433
;
run;
data table;
input table_id value;
cards;
221 301
322 202
433 713
;
run;
Data MAIN;
merge MAIN (in=a) TABLE;
by table_id;
if a;
run;
proc sql;
create view mainvw as
select t1.*,t2.value as value
from main as t1 left join table as t2
on t1.table_ID=t2.table_ID
;
quit;
When run in sequence with the merge followed by the left join, the view
maintains the value column attached by the merge and creates a new column
also named 'value'. This second column named 'value' disappears when storing
the view as a table.
The view of MAIN data with the value attached from the look-up table will
allow you to maintain the original MAIN dataset and attach the value when
required.
Sig
-----Original Message-----
From: Susie Li [mailto:Susie.Li@US.SANOFI.COM]
Sent: Tuesday, November 18, 2003 4:33 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: How to do table lookup in SAS when main dataset has multiple
records with the same key
Suppose I have my MAIN dataset (with fields: employee_id table_id), and the
lookup table in TABLE dataset (with fields: table_id value), and I want to
read the value from TABLE into MAIN for every employee:
Data MAIN;
merge MAIN (in=a) TABLE;
by table_id;
if a;
Above will update ONLY one record of the MAIN employees with the value of
the table_id.
Is there any other way to update all employees in MAIN with the same
table_id with its corresponding value in TABLE?
Important: The Information in this e-mail belongs to Sanofi-Synthelabo
Inc., is intended for the use of the individual or entity to which it is
addressed, and may contain information that is privileged, confidential, or
exempt from disclosure under applicable law. If you are not the intended
recipient, you are hereby notified that any disclosure, copying,
distribution, or use of, or reliance on, the contents of this e-mail is
prohibited. If you have received this e-mail in error, please notify us
immediately by replying back to the sending e-mail address, and delete this
e-mail message from your computer.
