05-05-2016 09:03 AM - edited 05-05-2016 09:04 AM
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
05-05-2016 09:27 AM
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;
Need further help from the community? Please ask a new question.