Code below consolidates the oracle tables and columns that don’t have blank values. I request your help in adding the content to perform the same on sas native tables by identifying the libname that is either oracle or sas)
Can you please help in transforming it by converting the code below into a macro that should accept the lib name as parameters?
LIBNAME jbl ORACLE PATH=ahbdwh SCHEMA=sys USER=jbl30 PASSWORD="{SAS002}05C6153E155987C63225469B" ;
LIBNAME jbl_jbl ORACLE PATH=ahbdwh SCHEMA=jbl30 USER=jbl30 PASSWORD="{SAS002}05C6153E155987C63225469B" ;
libname stat "c:\SASCONFIG\Lev1\SASApp\Data\stat";
%macro checkds(dsn);
%if %sysfunc(exist(&dsn)) %then %do;
data &dsn ;
set &dsn;
stop;
run;
%end;
%mend checkds;
%checkds(stat.tbl_column_count);
Proc sql noprint;
alter table stat.tbl_column_count modify table_name char(50);
quit;
Proc sql noprint;
create table tables as
select table_name from jbl.user_tables where num_rows>0 and table_name not like '%HIS%';
Create table alpha_num_columns as
select utc.table_name,utc.column_name from jbl.user_tab_columns UTC INNER JOIN TABLES T ON UTC.table_name=t.table_name
and utc.data_type not in ('NUMBER','DECIMAL');
Create table num_columns as
select utc.table_name,utc.column_name from jbl.user_tab_columns UTC INNER JOIN TABLES T ON UTC.table_name=t.table_name
and utc.data_type in ('NUMBER','DECIMAL');
quit;
Proc sql noprint;
select count(*) into :mv_count_table from tables;
quit;
%macro table_count;
%do i= 1 %to &mv_count_table;
data _null_;
set tables (firstobs=&i obs=&i);
call symputx ('mv_tab',table_name);
run;
Proc sql noprint;
create table table_alpha_num as
select column_name from alpha_num_columns where table_name="&mv_tab" ;
select count(*) into :CCount from table_alpha_num;
select count(*)into :avl from num_columns where table_name="&mv_tab" ;
select column_name into :Nname separated by ' ' from num_columns where table_name="&mv_tab" ;
quit;
%if &avl ne 0 %then %do;
Proc means data =jbl_jbl.&mv_tab nway noprint n;
output out=column_stat (where=(_stat_='N') keep=_stat_ &Nname );
run;
data column_stat;
set column_stat(Drop=_STAT_);
run;
Proc transpose data=column_stat out=num_column_count(keep=_name_ col1);
run;
Proc sql noprint;
alter table num_column_count modify _name_ char(256);
quit;
data num_column_count;
set num_column_count;
length table_name $50.;
table_name="&mv_tab";
run;
proc append base=stat.tbl_column_count data=num_column_count force;
run;
%end;
%If &CCount ne 0 %then %do;
data char;
set table_alpha_num ;
column="count("||column_name||") as "||column_name;
run;
Proc sql noprint;
select column into :column SEPARATED by ',' from char;
create table alpha_num_count as
select &column from jbl_jbl.&mv_tab;
quit;
Proc transpose data=alpha_num_count out=alpha_num_column_count(keep=_name_ col1);
run;
Proc sql noprint;
alter table alpha_num_column_count modify _name_ char(256);
quit;
data alpha_num_column_count;
set alpha_num_column_count;
length table_name $50.;
table_name="&mv_tab";
run;
proc append base=stat.tbl_column_count data=alpha_num_column_count force;
run;
%end;
data stat.tbl_column_count ;
set stat.tbl_column_count(where=(col1>0));
run;
%end;
%mend;
%table_count;
