Sample 25441: Demonstrates merging tables with PROC SQL
Please refer to the Full Code and the Results/Output tabs to see the code and results.
These sample files and code examples are provided by SAS Institute
Inc. "as is" without warranty of any kind, either express or implied, including
but not limited to the implied warranties of merchantability and fitness for a
particular purpose. Recipients acknowledge and agree that SAS Institute shall
not be liable for any damages whatsoever arising out of their use of this material.
In addition, SAS Institute will provide no support for the materials contained herein.
This sample is from the SAS Sample Library. For additional information refer to SAS Help and Online Documentation.
/****************************************************************/
/* S A S S A M P L E L I B R A R Y */
/* */
/* NAME: SQLFUN05 */
/* TITLE: Demonstrates Merging Tables with Proc SQL */
/* PRODUCT: BASE */
/* SYSTEM: ALL */
/* KEYS: SQL DATMAN WHERE SELECT SUBSTR MERGE SORT */
/* PROCS: SQL */
/* DATA: */
/* SUPPORT: UPDATE: */
/* REF: */
/* MISC: This example was contributed by Jack Hamilton */
/* of Amdahl Corporation, via BITNET. */
/* */
/* It demonstrates how PROC SQL can often provide */
/* a more concise solution to data processing */
/* problems. */
/* */
/****************************************************************/
/* Your mission(should you choose...) is to merge three tables, */
/* but ... */
/* */
/* the "common" variable has a different name in each table, */
/* */
/* the "common" variable has a different format, */
/* */
/* parts and customers have no P/C prefix in some tables */
/* but they do in others. */
data orders;
input cno $ pno $ qty;
cards;
C001 P001 10
C001 P002 20
C002 P003 30
C002 P002 20
C003 P003 50
;
data parts;
input no $ desc $ 4-20;
cards;
001 Part One
002 Part Two
003 Part Three
;
data cust;
input no $ name $ 4-20;
cards;
001 Cust One
002 Cust Two
003 Cust Three
;
/* The sql way */
proc sql;
select o.cno, c.name, o.pno, p.desc, o.qty
from orders o, parts p, cust c
where substr(cno, 2) = c.no
and substr(pno, 2) = p.no;
quit;
/* The traditional way */
data temp;
set orders;
cno = substr(cno, 2);
pno = substr(pno, 2);
run;
proc sort data=temp;
by cno;
run;
proc sort data=cust;
by no;
run;
data temp;
merge temp(in=t) cust(in=c rename=(no=cno));
by cno;
if t and c;
run;
proc sort data=temp;
by pno;
run;
proc sort data=parts;
by no;
run;
data temp;
merge temp(in=t) parts(in=p rename=(no=pno));
by pno;
if t and p;
run;
proc print;
run;
These sample files and code examples are provided by SAS Institute
Inc. "as is" without warranty of any kind, either express or implied, including
but not limited to the implied warranties of merchantability and fitness for a
particular purpose. Recipients acknowledge and agree that SAS Institute shall
not be liable for any damages whatsoever arising out of their use of this material.
In addition, SAS Institute will provide no support for the materials contained herein.
cno name pno desc qty
------------------------------------------------------------------
C001 Cust One P001 Part One 10
C001 Cust One P002 Part Two 20
C002 Cust Two P002 Part Two 20
C002 Cust Two P003 Part Three 30
C003 Cust Three P003 Part Three 50
Obs cno pno qty name desc
1 001 001 10 Cust One Part One
2 001 002 20 Cust One Part Two
3 002 002 20 Cust Two Part Two
4 002 003 30 Cust Two Part Three
5 003 003 50 Cust Three Part Three
This example demonstrates how PROC SQL can often provide a more concise solution to data processing problems.
| Type: | Sample |
| Topic: | SAS Reference ==> Procedures ==> SQL SAS Reference ==> Statements ==> File-handling ==> MERGE Common Programming Tasks ==> Combining Data Data Management ==> Manipulation and Transformation ==> Combining and Modifying Data Sets
|
| Date Modified: | 2005-08-24 16:06:32 |
| Date Created: | 2005-05-23 13:54:01 |
Operating System and Release Information
| SAS System | Base SAS | All | n/a | n/a |