topic Checking the contents of a Table in SAS everyday in SAS Programming

Checking the contents of a Table in SAS everyday

Rhino84 — Tue, 08 Aug 2023 18:38:02 GMT

I want to design a query which can check the contents of a Table every day in a SAS system.

The query should be able to :

- count the total no of records in the table everyday

- find the difference of records between yesterday and today.

- Check for valid values in a column.

Please help.

Re: Checking the contents of a Table in SAS everyday

sbxkoenk — Tue, 08 Aug 2023 21:48:11 GMT

Hello,

It's better to always mention the SAS version you are using.

(If you are using in-memory CAS tables in SAS Viya, the responses might be a bit different
... compared to V9 *.sas7bdat on-disk tables).

To know about your SAS version, submit:

%PUT &=sysvlong4;

1. To count the total no of records in the table everyday

Use this piece of code :

%LET Table2LevelName=SASHELP.DMINE;

data _NULL_;
 if 0 then set &Table2LevelName. nobs=count;
 call symput('NumberOfRows',trim(left(put(count,12.))));
 STOP;
run;

%PUT &=NumberOfRows;

2. find the difference of records between yesterday and today.
Use PROC COMPARE
SAS® 9.4 and SAS® Viya® 3.5 Programming Documentation
Base SAS Procedures Guide
COMPARE Procedure
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/n0c1y14wyd3u7yn1dmfcpaejllsn.htm

3. Check for valid values in a column.

Please give more info. I can think of 1000 ways to do this depending on what exactly is required.

BR, Koen

Re: Checking the contents of a Table in SAS everyday

ballardw — Tue, 08 Aug 2023 22:31:57 GMT

@Rhino84 wrote:

- find the difference of records between yesterday and today.

Will you have yesterday's data set? If not you you will need to save the count from the previous day

- Check for valid values in a column.

Valid values? depends on your definition. If you have specific rules share them.

Generally I enforce this with proper informats when reading values into SAS so that unacceptable values are treated as "invalid data" for specific values or report on ranges after reading. Depends on what you expect/mean by "check for valid" and what to do when encountering not valid values.

Re: Checking the contents of a Table in SAS everyday

Ksharp — Wed, 09 Aug 2023 11:27:31 GMT


/*Here is an example you can start with*/

%let before= D:\XiaKeShan\sas_file_WM- ETcath200_Datasets_2023_05_15 09_05_50  ;  *the path for yesterday dataset;
%let after=  D:\XiaKeShan\sas_file_WM- ETbox200_Datasets_2023_05_15 09_05_17   ;  *the path for today dataset;

libname before v9 "&before.";
libname after v9 "&after.";

%macro compare_dsn(first=,second= , output=);
proc sql;
create table &output. as
select * from  &second.
except
select * from  &first.
;
quit;
%mend;
proc datasets library=work kill memtype=data nolist nodetails;
quit;
data _null_;
 set sashelp.vtable(keep=memname libname where=(libname='AFTER'));
 call execute(catt('%compare_dsn(first=before.',memname,',second=after.',memname,', output=',memname,')'));
run;
proc sql; 
select memname label='Table Name',ifc(nlobs=0,'equal      ','not equal') as equal label='isEqual'
 from dictionary.tables 
  where libname='WORK' ;
quit;