I've 20 datasets in one library. Everyday I'm opening that dataset and used to check the no.of observations and whether the dataset is updated with current timestamp. Those datasets were everyday replaced with new data based on other input datasets. Is there a way to write a program to do this validation and generate the output as follows? Incase if any of those datasets (or all) are not updated with current date then I want the email to trigger. Appericiate if someone of you help me here.
Dataset | No_of_obs | X_current_date |
Employee | 12568 | 6-Dec-18 |
Marketing | 12578 | 6-Dec-18 |
Finanace | 32568 | 6-Dec-18 |
Retrieve the information from dictionary.tables (or sashelp.vtable).
As has been mentioned several times before, please post test data for all datasets in the form of a datastep - we are not here to do data entry or guess what needs to be done. Show what should be the output. Show what code you have written so far. Post any errors or warnings you might have.
All I would say from what you posted is data _null_ step, setting your base dataset, and generating some statements from that would seem to be the way forward.
@Babloo wrote:
How to retrieve the current date variable which was available in each of
the data set? Value of current date will be same for all the observations
in each of the dataset.
Did you check the sashelp data set? Do you have to use the date variable in the file, or is the creation or modified date ok?
Please go back and mark some of your previous questions as solved, correctly indicating the solution.
People have taken a lot of time to assist you on here, and acknowledging that would be appropriate.
@Babloo wrote:
How to retrieve the current date variable which was available in each of
the data set? Value of current date will be same for all the observations
in each of the dataset.
That was not part of your original question.
For example to create a data set with the creation and modified dates of the data set for members of a library:
proc sql; create table work.status as select libname, memname,crdate, modate, nobs from dictionary.tables where libname='WORK' ; quit;
The LIBNAME is stored in upper case so make sure you use either upcase text or the UPCASE function on the value you use.
You can modify this to only return likely candidates based on either the CRDATE (creation) or MODATE (modified date) values. These variables are datetime so if you wanted the list of members that were modified on or after a given date something like
proc sql; create table work.status as select libname, memname,crdate, modate, nobs from dictionary.tables where libname='WORK' and datepart(crdate) > '05DEC2018'd; quit;
If your "current date" variable is prior to the date of creation or modification there might be an issue to investigate in the assignment of that variable.
I ran the code as below and could see the missing values for the variables crdate, modate,nobs. Could this be due DB2 tables registered in SAS?
proc sql;
create table work.status as
select *
from dictionary.tables
where libname='DB2CRFS' ;
quit;
Sample output:
libname | memname | memtype | dbms_memtype | memlabel | typemem | crdate | modate | nobs |
DB2CRFS | DEMSY_CODETABLES | DATA | TABLE | DATA | . | . | . |
I tried the following code as well but stil not seeing the values for crdate, modate and nobs.
proc sql noprint;
connect to db2 (datasrc=db2gtu
authdomain=DB2AuthODS
connection=global);
create table work.status as
select *
from dictionary.tables
where libname='DB2CRFS';
disconnect from db2;
quit;
Extract the data to SAS, then run that code. Or alternatively passthrough some SQL code to the database which gets the information required and then returns it, maybe something like:
proc sql; connect to db2 (...); select * from connection to db2 ( select * from dictionary.tables); disconnect from db2; quit;
Please try sashelp.vtable in a data step as below , check the screen shot
so if you test this code you will get a dataset want with the variables displayed which should help you.
for your requirement change the library and keep the required variables.
data want;
set sashelp.vtable;
where libname='SDTM';
run;
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 25. 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.