DATA Step, Macro, Functions and more

ERROR: Duplicate values not allowed on index IMB_CODE for file FINALDATA.

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

ERROR: Duplicate values not allowed on index IMB_CODE for file FINALDATA.

[ Edited ]

 

Hi.  I have a macro that builds QueryData and appends it to FinalData repeatedly.  After the first append I want to build a unique index by imb_code to prevent the same imb_code from being appended to FinalData in future iterations.

 

I'm getting an error that seems to be failing the program and keeping the unique index from being created (I think):

 

ERROR: Duplicate values not allowed on index IMB_CODE for file FINALDATA.

 

Does anyone know why I'm unable to create the unique index?

 

 

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


* RULE QUERIES. Build and execute rule queries based on the rules defined in QueryRules (one at a time looping) ; %macro BuildQueryData(analysis_desc= , rule= , rule_order= ); proc sql; create table QueryData as ( select %str(%')&analysis_desc.%str(%') as RULE_NM, b.actual_dlvry_date as AD_DT, b.imb_code, &rule_order as rule_order from iv_ora.bi_spm_piece_recon a, bids_ora.bi_spm_piece_recon b where a.imb_code = b.imb_code and &rule ); quit; * Append datasets to final dataset ; proc append base=FinalData data=QueryData force; run; * Create unique index (if not exist), to meet the criteria that only one record (imb) per ; * rule is kept and counted in the data ; data _null_; set sashelp.vmember (where=(Libname='WORK' and memname='FINALDATA')); if upcase(index) = 'NO' then do; put 'NO, INDEX DOES NOT EXIST!'; call execute('%CreateUniqueIndex'); end; RUN; %mend BuildQueryData;

 

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_workAFDE01A901A4_imapsrac1
                                  Filename           /saswork/SAS_workAFDE01A901A4_imapsrac1
                                  Inode Number       147500
                                  Access Permission  rwx------
                                  Owner Name         ssbuechl
                                  File Size (bytes)  256


                                                Member       File
                                 #  Name        Type         Size  Last Modified

                                 1  FINALDATA   DATA      5292032  04May16:08:23:09
                                 2  QUERYDATA   DATA      5292032  04May16:08:23:08
                                 3  QUERYRULES  DATA        73728  04May16:08:20:26
                                 4  SASMACR     CATALOG     12288  04May16:08:20:26
1   +                    MODIFY FinalData;
1   +                                          INDEX CREATE imb_code / UNIQUE NOMISS;
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.01 seconds
      cpu time            0.01 seconds

 


Accepted Solutions
Solution
‎05-04-2016 10:04 AM
Super User
Posts: 19,822

Re: ERROR: Duplicate values not allowed on index IMB_CODE for file FINALDATA.

Posted in reply to buechler66

Check your dataset for duplicate values of IMB_CODE. You've stated it shouldn't have duplicates between runs, but can the first dataset have duplicates? If so that's not a unique index.

View solution in original post


All Replies
Solution
‎05-04-2016 10:04 AM
Super User
Posts: 19,822

Re: ERROR: Duplicate values not allowed on index IMB_CODE for file FINALDATA.

Posted in reply to buechler66

Check your dataset for duplicate values of IMB_CODE. You've stated it shouldn't have duplicates between runs, but can the first dataset have duplicates? If so that's not a unique index.

Regular Contributor
Posts: 212

Re: ERROR: Duplicate values not allowed on index IMB_CODE for file FINALDATA.

Posted in reply to buechler66
Yes, thank you!
☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 444 views
  • 0 likes
  • 2 in conversation