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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

2 REPLIES 2
Reeza
Super User

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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