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
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.