Date: Tue, 11 Mar 1997 15:51:03 CST
Reply-To: Undetermined origin c/o LISTSERV maintainer
<owner-LISTSERV@AKH-WIEN.AC.AT>
Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From: Undetermined origin c/o LISTSERV maintainer
<owner-LISTSERV@AKH-WIEN.AC.AT>
Subject: I'm no expert, but....
I'm running SAS 6.09 on an HP/UX system. I'm trying to do what should
be a simple merge between 5 databases, by two variables. I'm getting the
dreaded "NOTE: MERGE statement has more than one data set with repeats of BY
values." message when I try to put the datasets together. I even went so far as
to de-dup the databases, based on the merge keys, and I still get the same
message.
I've attached the log below which shows the problem. The output file NOTX95CM
should have the same number of records as STANDARD.
HELP!?!?!
Bruce Johnson
bjohnson@sachs.com
NOTE: LOG Edited for confidential content and size
1 The SAS System
17:50 Monday, March 10, 1997
NOTE: Copyright(c) 1989 by SAS Institute Inc., Cary, NC USA.
NOTE: SAS (r) Proprietary Software Release 6.09 TS027
NOTE: SAS initialization used:
real time 0.51 seconds
cpu time 0.09 seconds
2 The SAS System
17:50 Monday, March 10, 1997
NOTE: AUTOEXEC processing completed.
1
*----------------------------------------------------------------------;
2 * Program: MERGE SAS
;
3 * ;
4 * Created: 03/05/97
;
*----------------------------------------------------------------------;
15
16
**************************************************************************;
17 ** assign the libnames to access the raw data and proc/diag/drg
formats **;
18
**************************************************************************;
19 libname sasfmt v609 '/templates/formats';
NOTE: Libname SASFMT refers to the same physical library as SASFORM.
NOTE: Libref SASFMT was successfully assigned as follows:
Engine: V609
Physical Name: /templates/formats
20 libname sasdata v609 '/cldata/chtn/notx';
NOTE: Libref SASDATA was successfully assigned as follows:
Engine: V609
Physical Name: /cldata/chtn/notx
21
22 ***************************************;
23 ** do not print all include sas code **;
24 ***************************************;
25 OPTIONS ERRORS=1 NOSOURCE2;
26
3 The SAS System
17:50 Monday, March 10, 1997
Compressed is 1263 pages; un-compressed would require 2815 pages.
NOTE: PROCEDURE FORMAT used:
real time 10.54 seconds
cpu time 7.91 seconds
2603 RUN;
2604
2605 *******************************;
2606 * name of inpatient database **;
2607 * with a libname and without **;
2608 *******************************;
2609 %LET INFILE=SASDATA.notx95cm;
2610 %LET INDSET=notx95cm;
2611
2612 ********************************;
2613 * Sort the necessary files *;
2614 * prior to merge *;
2615 ********************************;
2616 PROC SORT DATA=SASDATA.standard nodupkey;
2617 BY HOSPID PATID;
2618 RUN;
NOTE: 236 observations with duplicate key values were deleted.
NOTE: The data set SASDATA.STANDARD has 1495197 observations and 42 variables.
NOTE: Compressing data set SASDATA.STANDARD decreased size by 9.81 percent.
Compressed is 28094 pages; un-compressed would require 31151 pages.
NOTE: PROCEDURE SORT used:
real time 4:20.96
cpu time 2:57.04
2619
2620 PROC SORT DATA=SASDATA.px nodupkey;
2621 BY HOSPID PATID;
2622 RUN;
NOTE: 14 observations with duplicate key values were deleted.
NOTE: The data set SASDATA.PX has 937548 observations and 49 variables.
NOTE: Compressing data set SASDATA.PX decreased size by 31.30 percent.
Compressed is 13145 pages; un-compressed would require 19135 pages.
NOTE: PROCEDURE SORT used:
real time 2:39.27
cpu time 1:37.75
2623
2624 PROC SORT DATA=SASDATA.dx nodupkey;
2625 BY HOSPID PATID;
2626 RUN;
NOTE: 23 observations with duplicate key values were deleted.
NOTE: The data set SASDATA.DX has 1495410 observations and 19 variables.
NOTE: Compressing data set SASDATA.DX decreased size by 42.69 percent.
Compressed is 11130 pages; un-compressed would require 19422 pages.
NOTE: PROCEDURE SORT used:
real time 3:05.68
4 The SAS System
17:50 Monday, March 10, 1997
cpu time 2:02.28
2627
2628 PROC SORT DATA=SASDATA.payer nodupkey;
2629 BY HOSPID PATID;
2630 RUN;
NOTE: 0 observations with duplicate key values were deleted.
NOTE: The data set SASDATA.PAYER has 237998 observations and 8 variables.
NOTE: Compressing data set SASDATA.PAYER decreased size by 27.58 percent.
Compressed is 1402 pages; un-compressed would require 1936 pages.
NOTE: PROCEDURE SORT used:
real time 21.53 seconds
cpu time 16.53 seconds
2631
2632 PROC SORT DATA=SASDATA.phys nodupkey;
2633 BY HOSPID PATID;
2634 RUN;
NOTE: 675 observations with duplicate key values were deleted.
NOTE: The data set SASDATA.PHYS has 1425274 observations and 6 variables.
NOTE: Compressing data set SASDATA.PHYS decreased size by 32.63 percent.
Compressed is 8974 pages; un-compressed would require 13321 pages.
NOTE: PROCEDURE SORT used:
real time 2:36.76
cpu time 1:50.03
2635
2636 ******************************************;
2637 ** Merge the 5 files by hospid and patid *;
2638 ******************************************;
2639 DATA &infile;
2640 merge sasdata.standard(in=a)
2641 sasdata.px
2642 sasdata.dx
2643 sasdata.payer
2644 sasdata.phys
2645 ;
2646 BY HOSPID PATID;
2647 IF a;
2648 ;
2649
2650 rename charge = totchg;
2651 format ccflag $ccflag.
2652 charge vchg.
2653 ;
2654 drop seq;
2655
2656 DROP SECDX1 SECPROC1;
2657 RENAME OPNON1=PROCCLAS PXDAYS1=PROCDAY;
2658 LABEL pxdays1="Days to Primary Proc."
2659 opnon1="Operative Class for Primary Proc."
2660 charge="Total Charges"
5 The SAS System
17:50 Monday, March 10, 1997
2661 ;
2662 RENAME SECDX2=SECDX1 SECDX3=SECDX2 SECDX4=SECDX3 SECDX5=SECDX4
2663 SECDX6=SECDX5 SECDX7=SECDX6 SECDX8=SECDX7
2664 SECDX9=SECDX8 SECDX10=SECDX9 SECDX11=SECDX10
2665 SECDX12=SECDX11 SECDX13=SECDX12 SECDX14=SECDX13
2666 SECDX15=SECDX14 SECDX16=SECDX15;
2667 RENAME SECPROC2=SECPROC1 SECPROC3=SECPROC2 SECPROC4=SECPROC3
SECPROC5=SECPROC4
2668 SECPROC6=SECPROC5 SECPROC7=SECPROC6 SECPROC8=SECPROC7
2669 SECPROC9=SECPROC8 SECPRC10=SECPROC9 SECPRC11=SECPRC10
2670 SECPRC12=SECPRC11 SECPRC13=SECPRC12 SECPRC14=SECPRC13
2671 SECPRC15=SECPRC14;
2672 RENAME PXDAYS2=SECDAY1 PXDAYS3=SECDAY2 PXDAYS4=SECDAY3
PXDAYS5=SECDAY4
2673 PXDAYS6=SECDAY5 PXDAYS7=SECDAY6 PXDAYS8=SECDAY7
2674 PXDAYS9=SECDAY8 PXDAYS10=SECDAY9 PXDAYS11=SECDAY10
2675 PXDAYS12=SECDAY11 PXDAYS13=SECDAY12 PXDAYS14=SECDAY13
2676 PXDAYS15=SECDAY14;
2677 RENAME OPNON2=SECCLS1 OPNON3=SECCLS2 OPNON4=SECCLS3 OPNON5=SECCLS4
2678 OPNON6=SECCLS5 OPNON7=SECCLS6 OPNON8=SECCLS7
2679 OPNON9=SECCLS8 OPNON10=SECCLS9 OPNON11=SECCLS10
2680 OPNON12=SECCLS11 OPNON13=SECCLS12 OPNON14=SECCLS13
2681 OPNON15=SECCLS14;
2682 RUN;
WARNING: The variable SECDX16 in the DROP, KEEP, or RENAME list has never been
referenced.
NOTE: MERGE statement has more than one data set with repeats of BY values.
NOTE: The data set SASDATA.NOTX95CM has 1495433 observations and 109 variables.
NOTE: Compressing data set SASDATA.NOTX95CM decreased size by 46.57 percent.
Compressed is 15367 pages; un-compressed would require 28759 pages.
NOTE: DATA statement used:
real time 9:02.71
cpu time 8:37.76
NOTE: The SAS System used:
real time 42:45.34
cpu time 36:49.16
NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414
