DATA Step, Macro, Functions and more

More troubles with Indexes

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

More troubles with Indexes

[ Edited ]

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

 


Accepted Solutions
Solution
‎05-05-2016 09:47 AM
Valued Guide
Posts: 2,175

Re: More troubles with Indexes

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

View solution in original post


All Replies
Solution
‎05-05-2016 09:47 AM
Valued Guide
Posts: 2,175

Re: More troubles with Indexes

a composite index is defined like
INDEX CREATE key=( firstVar secondVar) /unique ;
Regular Contributor
Posts: 212

Re: More troubles with Indexes

Thanks very much!
Super User
Super User
Posts: 7,401

Re: More troubles with Indexes

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 210 views
  • 1 like
  • 3 in conversation