topic Re: More troubles with Indexes in SAS Programming

More troubles with Indexes

buechler66 — Thu, 05 May 2016 13:04:41 GMT

Hi. I'm trying to create a unique index for both spm_calc_batch_date and imb_code together (composite). The error message I'm getting has me thinking I've created two individual unique indexes instead of one. I'm trying to reject records with the same spm_calc_batch_date and imb_code. Am I doing something wrong?

* Macro to create Unique Index ;
%macro CreateUniqueIndex;
	PROC DATASETS;
	MODIFY FinalData;
	INDEX CREATE spm_calc_batch_date imb_code / UNIQUE NOMISS;
	run;
%mend CreateUniqueIndex;

MLOGIC(CREATEUNIQUEINDEX):  Beginning execution.
MLOGIC(CREATEUNIQUEINDEX):  Ending execution.
NOTE: There were 1 observations read from the data set SASHELP.VMEMBER.
      WHERE (Libname='WORK') and (memname='FINALDATA');

NOTE: CALL EXECUTE generated line.
1   + PROC DATASETS;
                                                          Directory

                                  Libref             WORK
                                  Engine             V9
                                  Physical Name      /saswork/SAS_work014A01900110_imapsrac1
                                  Filename           /saswork/SAS_work014A01900110_imapsrac1
                                  Inode Number       147496
                                  Access Permission  rwx------
                                  Owner Name         ssbuechl
                                  File Size (bytes)  256


                                                Member      File
                                 #  Name        Type        Size  Last Modified

                                 1  FINALDATA   DATA      966656  05May16:07:22:54
                                 2  QUERYDATA   DATA      966656  05May16:07:22:54
                                 3  QUERYRULES  DATA       73728  05May16:07:20:48
                                 4  SASMACR     CATALOG    12288  05May16:07:20:47
1   +                    MODIFY FinalData;
1   +                                          INDEX CREATE spm_calc_batch_date imb_code / UNIQUE NOMISS;
ERROR: Duplicate values not allowed on index SPM_CALC_BATCH_DATE for file FINALDATA.
ERROR: Duplicate values not allowed on index IMB_CODE for file FINALDATA.
1   +                                                                                                         run;

NOTE: Statements not processed because of errors noted above.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds

Re: More troubles with Indexes

Peter_C — Thu, 05 May 2016 13:12:57 GMT

a composite index is defined like
INDEX CREATE key=( firstVar secondVar) /unique ;

Re: More troubles with Indexes

RW9 — Thu, 05 May 2016 13:27:57 GMT

Hi,

You are setting an index with the option unique, and it is telling you there are duplicate data in that column of data. Remove the duplicates so the column is unique. Also, why is the code in a macro, it doesn't appear to do anything other than add several lines to your code. Also, please avoid all caps in coding:

proc sort data=finaldata dupout=dups nodupkey;
  by spm_calc_batch_date;
run;

The above will show you the duplicate rows of data.

To quantify my comment on caps and no macro, this code is exactly the same as wht you do:

proc datasets;
  modify finaldata;
  index create spm_calc_batch_date imb_code / unique nomiss;
run;

Re: More troubles with Indexes

buechler66 — Thu, 05 May 2016 13:47:10 GMT

Thanks very much!