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

buechler66 — Wed, 04 May 2016 13:49:59 GMT

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):

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

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

Reeza — Wed, 04 May 2016 13:54:41 GMT

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.

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

buechler66 — Wed, 04 May 2016 14:06:36 GMT

Yes, thank you!

topic Re: ERROR: Duplicate values not allowed on index IMB_CODE for file FINALDATA. in SAS Programming

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

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

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