How to check all tables and insert in one table ?

[Cap2010]

Want to check each table in the database "db"
whether any table are having missing data or blanks data do not know how many tables are there as they are created while running application.
for that created a table CheckTb with field Tname and Counter


do not know how to run and check for all the tables. I believe either have to use macro
want to insert checking all tables and store final value in one table if the value exist

below is the code for same

proc sql;
create table db.CheckTb(tName char(20), counter char(20));
quit;

proc sql;
insert into test
select count(date) as TCnt from MtrlTransac where date is missing or date =.;
libname tmp "&lib";
quit;

 

[klaz2002]

Cap2010,

The first thing that your SAS program will need is a list of all the tables in th 'db' database. You can get this information from a PROC CONTENTS with an OUT=your_dataset_nm option. You then use the proc sort to eliminate duplicate table names (you can do this with proc sql too). Once you have the list of table names you generate them as macro values. After you have the macro 'list' you can loop through the list and run you data checks.

Ex.

proc contents data=db._all_ out=temp;
run;
proc sort
data=temp (keep=memname)
out =temp2 nodupkey;
by memname;
run;
data _null_;
set temp2 end=last;
call symput('tbl_nm'||trim(left(put(_n_,8.))),trim(left(memname)) );
if last then
call symput('limit',trim(left(put(_n_,8.))) );
run;
** YOU NOW HAVE A MACRO LIST THAT HAS THE FORMAT TBL_NMx **;
** REMEMBER LIMIT IS YOU LOOP LIMIT;

You would use the info as follows;

%macro data_check;
%do i=1 %to &limit;
....check you data
data test;
set &&tbl_nm&i;

... your data checking;
run;
%end;
%mend data_check;

You may need another schema to tackle all the vars in the table, but this should get you started.

I hope that this helps you.

Klaz

 

[Cap2010]

Klaz,

It works with the command, and have done below changes

%macro data_check;
%do i=1 %to &limit;

select count(date) as TCnt from work.&temp2(i) where date is missing or date =.;

data test;
set &&tbl_nm&i;

insert into db.checktb(tname,counter)
values( work.&tbl_nm&i, &&count(date) &)

run;
%end;
%mend data_check;

There are no records in checktb. something is missing ?.

 

[klaz2002]

From what I see you are mixing the SQL code with DataStep code. If you want to check to see if a table has any records you could use the %sysfunc withe exist macro function call. You need to tell us more of what you want to do.
Klaz

 

[Cap2010]

I need to check which table has . or missing value and
store in a table with number of such records in each table.

Cap2010

 

[klaz2002]

Cap2010,
Ok, I understand what you need done. You want a count of missing records for all your tables. Why not try the following:

Get your table names. (Using the code I gave you)

%macro get_num_of_missing;
proc contents data=db._all_ out=temp;
run;
proc sort
data=temp (keep=memname)
out =temp2 nodupkey;
by memname;
run;
data _null_;
set temp2 end=last;
call symput('tbl_nm'||trim(left(put(_n_,8.))),trim(left(memname)) );
if last then
call symput('limit',trim(left(put(_n_,8.))) );
run;
** YOU NOW HAVE A MACRO LIST THAT HAS THE FORMAT TBL_NMx **;
** REMEMBER LIMIT IS YOU LOOP LIMIT
NOW LOOP THROUGH YOUR TABLES AND DO THE FOLLOWING.;

%do i=1 %to &loop;
data temp&i(keep=table_name);
set &&tbl_nm&i;
length table_name $20;
table_name="&&tbl_nm&i";
where your_var = .;
run;
proc append base = all_data data=temp&i;
run;
%end;
proc sql;
create table final as
select table_name, count(table_name) as num_missing
from all_data
group by table_name;
quit;
proc print data=final;
run;
proc datasets ;
delete all_data;
quit;
%mend;

Klaz

 

[Cap2010]

Klaz,

Thanks it worked with changes in word &loop to &limit.

now it gives this error

ERROR: File WORK.Material.DATA does not exist

This table does not exist in work, it exist in db. and checked final table it was 0 value.

Cap2010

 

[klaz2002]

In the data step that reads tha source table, you must put the sas lib name in the SET statement. (Have you done that?) Please submit the code that you worked with.
ex.
set db.&&temp&i;
.....

Hope this helps you.
Klaz

 

[Cap2010]

Klaz,

it worked with some correction. see comments in red
cap2010
option notes source;
%macro get_num_of_missing;

proc contents data=db._all_ out=temp;
run;
proc sort
data=temp (keep=memname)
out =temp2 nodupkey;
by memname;
run;
data _null_;
set temp2 end=last;
call symput('tbl_nm'||trim(left(put(_n_,8.))),trim(left(memname)) );
if last then
call symput('limit',trim(left(put(_n_,8.))) );
run;


%do i=1 %to &limit;
data work.temp&i(keep=table_name);
set db.&&tbl_nm&i;
length table_name $20;
table_name="&&tbl_nm&i";
where Date = . or Date is missing;
Field name will vary will be Date DATE or TDate or TDATE

run;
proc append base = all_data data=temp&i;
run;
%end;
proc sql;
create table db.final as
select table_name, count(table_name) as num_missing
from all_data
group by table_name;
quit;
proc print data=final;
run;
proc datasets ;
delete all_data;
quit;
%mend;

*Invoke Macro *;
%get_num_of_missing;