My problem is similar to the posts "Merging two datasets with missing values in one" and "Table lookup puzzle"

I currently use several Oracle Database with similar data housed in them (common fields in several). At recent conference, the questioned was posted on how to bring all the databases together, so that the data for a particular WIDGETs can be viewed in one database instead of looking at separately. I have included the log of what I have accomplished so far.

Sorry for the length of the post, but wanted to provide as much information as possible.

NOTE: PROCEDURE PRINTTO used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

2505

2506 DATA ONE ;

2507 INFILE CARDS ;

2508 INPUT WIDGET_TYPE WIDGET_NAME $ WIDGET_CODE $ WIDGET_X $ ;

2509 CARDS ;

NOTE: The data set WORK.ONE has 3 observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

2513 ;

2514

2515

2516 DATA TWO ;

2517 INFILE CARDS ;

2518 INPUT WIDGET_TYPE WIDGET_NAME $ WIDGET_CODE $ WIDGET_Y $ ;

2519 CARDS ;

NOTE: The data set WORK.TWO has 3 observations and 4 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

2523 ;

2524

2525 DATA GET_WITH_DATA_STEP ;

2526 SET ONE

2527 TWO ;

2528

NOTE: There were 3 observations read from the data set WORK.ONE.

NOTE: There were 3 observations read from the data set WORK.TWO.

NOTE: The data set WORK.GET_WITH_DATA_STEP has 6 observations and 5 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

2529 PROC SORT

2530 DATA = GET_WITH_DATA_STEP ;

2531 BY WIDGET_NAME WIDGET_TYPE ;

2532

2533 RUN ;

NOTE: There were 6 observations read from the data set WORK.GET_WITH_DATA_STEP.

NOTE: The data set WORK.GET_WITH_DATA_STEP has 6 observations and 5 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

2534

2535 PROC SQL ;

2536 CREATE TABLE GET_WITH_SQL AS

2537 SELECT A.* , B.*

2538 FROM ONE AS A ,

2539 TWO AS B

2540 ORDER BY WIDGET_TYPE ;

WARNING: Column named WIDGET_TYPE is duplicated in a select expression (or a view). Explicit

references to it will be to the first one.

NOTE: The execution of this query involves performing one or more Cartesian product joins that

can not be optimized.

WARNING: Variable WIDGET_TYPE already exists on file WORK.GET_WITH_SQL.

WARNING: Variable WIDGET_NAME already exists on file WORK.GET_WITH_SQL.

WARNING: Variable WIDGET_CODE already exists on file WORK.GET_WITH_SQL.

NOTE: Table WORK.GET_WITH_SQL created, with 9 rows and 5 columns.

2541 QUIT ;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

2542

2543 PROC PRINT

2544 DATA = GET_WITH_DATA_STEP ;

2545

The SAS System 07:38 Friday, September 5, 2008 401

WIDGET_ WIDGET_ WIDGET_

Obs TYPE NAME CODE WIDGET_X WIDGET_Y

1 11 EE HH XX

2 22 EE HH YY

3 11 QQ RR XX

4 22 QQ RR YY

5 11 WW FF XX

6 22 WW FF YY

NOTE: There were 6 observations read from the data set WORK.GET_WITH_DATA_STEP.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

2546 PROC PRINT

2547 DATA = GET_WITH_SQL ;

2548

The SAS System 07:38 Friday, September 5, 2008 402

WIDGET_ WIDGET_ WIDGET_

Obs TYPE NAME CODE WIDGET_X WIDGET_Y

1 11 WW FF XX YY

2 11 WW FF XX YY

3 11 QQ RR XX YY

4 11 EE HH XX YY

5 11 EE HH XX YY

6 11 QQ RR XX YY

7 11 QQ RR XX YY

8 11 WW FF XX YY

9 11 EE HH XX YY

NOTE: There were 9 observations read from the data set WORK.GET_WITH_SQL.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

2549 PROC PRINTTO LOG = LOG ;

2550

2551 RUN ;

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden