Year    Report_ID   BAD_PART_NUMBERS
2015    P12568  6989820
2015    P12568  1769819
2015    P12568  1988700
2015    P12697  879010
2015    P12697  287932
2015    P12697  17902

order_no    Customer_id Purchase dt PART_NUM    PART_DESC
265187  B1792   3/4/2016    02-6989820  gfsahj
1669    B1792   7/8/2017    01-32769237 susisd
1692191 B1794   5/7/2015    03-6989820  gfsahj
16891   B1794   3/24/2016   78-1769819  ysatua
62919   B1794   2/7/2017    15-3287629  at8a9s7d

%let REPORT_ID=('P12568');
 Proc SQL;
 connect to teradata as tera1 (server='XXX' user=&userid pwd=&pwd Database
 ="XXXXX" );
create table BAD_PART as
select * from connection to tera1
(
 select REPORT_ID,REPORT_DESC from REPORTS where REPORT_ID=&REPORT_ID
 *other where conditions
 group by 1,2
 )
;
disconnect from tera1;
quit;
/*creating a PART_NUM macro*/
PROC SQL NOPRINT;
SELECT quote(cats('%',PART_NUM),"'")
INTO :PART_NUM separated by ", "
FROM BAD_PART ;
QUIT;

%put macro variable PART_NUM:&PART_NUM;

/*FINDING SECONDARY PART INFORMATION*/
proc sql;
 connect to teradata as tera1 (server='XXXX' user=&userid pwd=&pwd Database
 =" XXXX" );
create table SEC_PART as
select * from connection to tera1
(
SELECT  &REPORT_ID as REPORT_ID, PART_NUM, PART_DESC,COUNT (DISTINCT ORDER)
as frequency
       from (
select  Customer_id,Min(Purchase_dt)  as FIRST_BAD_PART_PURCHASE
from    ORDERS
where       (PART_NUM like any(&PART_NUM)) A
left join (
select  Customer_id, Purchase_dt, PART_NUM, PART_DESC,ORDER
from    ORDERS group by 1,2,3,4,5 ) B
                        on A. Customer_id =B. Customer_id
    AND FIRST_BAD_PART_PURCHASE< Purchase_dt
group by 1,2,3 order by frequency desc
having frequency>0
)
;
disconnect from tera1;
quit;

/*---various PROC SQL and Data steps*/

Report_ID  MONTHS  VALUE
 P12568     0       21
 P12568     1       34
 P12568     2       40.38
 P12568     3       67.05
 P12568     4       100.08

select REPORT_ID from reports where year='2015'.

JOIN ( select part_num bad_part_num from <bad part_num query> ) bad_list ON
PART_NUM like '%' || bad_list.bad_part_num

where       (PART_NUM like any(&PART_NUM)) A