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
