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.
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
@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.
/*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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: