Below is the sample code. My calculations are going wrong. I am still trying from my side. DATA Master; length Table_Name Column_Name $10; INPUT Table_Name $ Column_Name $ Key $; cards; Customer Cust_ID Y Customer Cust_Type Y Product Product_ID Y Product Cust_ID Y Offer Offer_ID Y ; run; DATA Customer; length Cust_ID Customer_Name Cust_Type Cust_Area $10; INPUT Cust_ID $ Customer_Name $ Cust_Type $ Cust_Area $; cards; 101 Amit Retail Munbai 102 Rahul CIC Hyd 10300 Sachin Retail Dheli 104 Dhoni Retail Pune 105 Virat CIC Hyd 106 Rohit Retail Mumbai 107 James CIC Chennai 108 Kartik Retail kerala 109000 Priyanka Retail Mumbai 110 Maneesha Retail Mumbai ; run; DATA Customer_BKP; length Cust_ID Customer_Name Cust_Type Cust_Area $10; INPUT Cust_ID $ Customer_Name $ Cust_Type $ Cust_Area $; cards; 101 Amit Retail Munbai 102 Rahul CIC Hyd 103 Sachin Retail Dheli 104 Dhoni Retail Pune 105 Virat CIC Hyd 106 Rohit Retail Mumbai 107 James CIC_YY Chennai 108 Kartik Retail kerala 109 Priyanka Retail Mumbai 110 Maneesha Retail Mumbai ; run; options symbolgen; proc sql; select count(table_name) into: cnt from master; select distinct table_name into: tab1-:tab%eval(&cnt.) from master ; quit; proc sql; select table_name, column_name into: table_name, :column_name from master; quit; %put &table_name &column_name; proc sql; select table_name, column_name into: table_name_bkp, :column_name_bkp from master; quit; %put &table_name_bkp &column_name_bk; *** still working on it**; %macro loop(); data total;delete;run; %do i = 1 %to &cnt.; proc sql noprint; select distinct column_name into: cols separated by "," from master where key="Y" and Table_Name="&&tab&i.." ; create table Mds as select *, count(*) as key1 from ( select distinct &cols from &&tab&i..); create table bkp as select*, count(*) as key2 from ( select distinct &cols1 from &&tab&i.._bkp); create table per as select &&&cols&i.. as column_name, "&&tab&i.." as table_name, a.key1,b.key2, a.key1/b.key2 as percentage from mds as a , bkp as b; /* drop table mds;*/ /* drop table bkp;*/ quit; data total; set total per; run; %put &cols; %end; %mend; %loop(); **Since above code is not working properly started working with below**'; proc sql; create table xyz as select &column_name as name, count(*) as count from (select cust_id from &table_name except select cust_id from &table_name_bkp); quit; %put &cont;
... View more