********************************************************************************************************;
* SAS Note 54355 - Duplicate PROD_HIER_SK values across partitions in the Analytical Product Hierarchy *;
*                                                                                                      *;
* This program must be run in a SAS session where the SAS Merchandise Intelligence libraries are       *;
* allocated.                                                                                           *;
*                                                                                                      *;
* This program checks the partition level ATTR_PROD_DM table for all partitions and determines whehter *;
* the same PROD_HIER_SK value appears in multiple partitions.                                          *;
* A message is printed in the SAS log based on the findings.                                           *;
* There are duplicate PROD_HIER_SK values in partition ATTR_PROD_DM tables - indicates there are       *;
* duplicate PROD_HIER_SK values and the data needs to be fixed.                                        *;                                  
* There are no duplicate PROD_HIER_SK values in partition ATTR_PROD_DM tables - indicates the data is  *;
* ok and no corrective action is necessary.                                                            *;
* If duplicates are found they are printed.                                                            *
********************************************************************************************************;
***************** customize part_base macro variable to your site                                      *;
* define the base part of the partition directory;
%LET part_base = <location_of_your_di_dm>\1;

* get PROD_PARTITION_LVL global setting;
proc sql noprint;
select spec_value into :PROD_PARTITION_LVL
from di_dm.global_setting
where APP = 'ALL' and spec_nm = 'PROD_PARTITION_LVL';
quit;
run;
%put PROD_PARTITION_LVL = &PROD_PARTITION_LVL;

* get parition_ids from product_hier_assoc_dm;
proc sql noprint;
select strip(put(prod_hier_sk,20.)), strip(put(count(prod_hier_sk),20.))
  into :LIST_PARTITION_IDS separated by ' ', :NBR_PARTITIONS
from di_dm.product_hier_assoc_dm
where prod_hier_assoc_cd = 1 and prod_lvl = &prod_partition_lvl and prod_hier_sk = parent_prod_hier_sk; 
quit;
run;
%put NBR_PARTITIONS = &NBR_PARTITIONS;
%put LIST_PARTITION_IDS = &LIST_PARTITION_IDS;


%macro partition_attr(partition_id);
libname plib "&part_base/&partition_id";
data part_attr_prod_dm;
partition_id=&partition_id;
set plib.attr_prod_dm;
run;
%mend; /* partition_attr */

%macro padd;
proc append data=part_attr_prod_dm base=all_attr_prod_dm force; run;
%mend; /* padd */

%macro all_partitions;
proc datasets noprint;
delete all_attr_prod_dm;
quit;
run;
%DO I = 1 %TO &NBR_PARTITIONS;
   %LET partition_id = %SCAN(&LIST_PARTITION_IDS,&I,%STR( ));
   %PUT Processing partition_id = &PARTITION_ID;
   %partition_attr(&PARTITION_ID);
   %padd;
%END; /* do loop */
%mend; /* all_partitions */

%all_partitions;

proc sort data=all_attr_prod_dm out=temp dupout=dup_sks nodupkey; by prod_hier_sk; run;

data dup_sks;
set dup_sks;
keep prod_hier_sk;
if prod_lvl gt &PROD_PARTITION_LVL;
run;

proc sort data=dup_sks nodupkey; by prod_hier_sk; run;

proc sql noprint;
create table dup_attr_prod_dm as
select * 
from all_attr_prod_dm 
where prod_hier_sk in (select prod_hier_sk from dup_sks)
order by prod_hier_sk, partition_id;
quit;
run;

data _null_;
data_set_id = open('dup_attr_prod_dm');
nobs = attrn(data_set_id, 'nlobs');
put '********************************************************************************************';
if nobs > 0 then do;
   put 'There are duplicate PROD_HIER_SK values in partition ATTR_PROD_DM tables';
end;
else put 'There are no duplicate PROD_HIER_SK values in partition ATTR_PROD_DM tables';
put '********************************************************************************************';
run;

proc print data=dup_attr_prod_dm;
Title 'Duplicate PROD_HIER_SK values found in more than one partition ATTR_PROD_DM table';
run;