Code Optimisation and transformation help based on existing code?

[Allaluiah]

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;

[LinusH]

For optimization, point out what part(s) in the macro that suffers.

Since I don't know anything about the requirements for this macro, it's hard to tell what changes that can be made.

You can use the attrc() function with the 'ENGINE' parameter to find out which data format your table is in.