BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buechler66
Barite | Level 11

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Peter_C
Rhodochrosite | Level 12
a composite index is defined like
INDEX CREATE key=( firstVar secondVar) /unique ;

View solution in original post

3 REPLIES 3
Peter_C
Rhodochrosite | Level 12
a composite index is defined like
INDEX CREATE key=( firstVar secondVar) /unique ;
buechler66
Barite | Level 11
Thanks very much!
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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