topic Re: How to find which data sets getting updated daily when you connect to database using SAS libname in SAS Programming

How to find which data sets getting updated daily when you connect to database using SAS libname

buddha_d — Mon, 10 Feb 2020 20:15:23 GMT

I am using a libname statement to login to database to access tables using SAS. There are over 2000 tables. I am not sure which tables to use. I want to know which tables are getting updated regularly. Is there a way to check that?

This is the strategy I am applying. I am getting all tables information using proc contents and pulling tables that contain any date field (like name like '%DATE%';). Once those tables get pulled then I am looking for those fields that contain date in their names, then pull minimum date as well as maximum date for that specific table. Is there a better way to write macro obtain the results. There are tables which contain 10-15 date fields and I don't know how to code that to fit in macro. Could some one please help me with that. I have the following code:

options symbolgen mprint mlogic;

LIBNAME ONE SQLSVR DSN=CUSTOMER_DATAMART SCHEMA=DBO;

proc contents data= one._all_ out=new_one noprint;
run;

PROC SQL;
	CREATE TABLE TIMES AS 
	SELECT DISTINCT MEMNAME,NAME 
	FROM NEW_ONE
		WHERE UPCASE(NAME) LIKE '%DATE%';
QUIT;

PROC SQL;
	CREATE TABLE DS AS 
	SELECT DISTINCT MEMNAME
	FROM TIMES 
		WHERE UPCASE(MEMNAME) LIKE '%DATASETNAME%';
QUIT;

data _null_;
	set ds end=eof;
	call sumputx('dsn'||strip(put(_n_,5.)),memname);
	if eof then do;
	call symputx('n', strip(put(_n_,5.)));
	end;
run;

%macro check;
%DO I=1 %TO &N;
	PROC SQL NOPRINT;
		CREATE TABLE &&DSN&I (COMPRESS=YES) AS 
		SELECT MIN(DATE) AS MIN_DATE, MAX(DATE) AS MAX_DATE
		FROM ONE.&&DSN&I;
	QUIT;
%END;
%MEND;
%CHECK;

Thanks in advance

Re: How to find which data sets getting updated daily when you connect to database using SAS libname

Reeza — Mon, 10 Feb 2020 20:24:01 GMT

@buddha_d wrote:

I am using a libname statement to login to database to access tables using SAS. There are over 2000 tables. I am not sure which tables to use. I want to know which tables are getting updated regularly. Is there a way to check that?

Does your company not have any documentation on this? I feel like an email to the DB owners would get you this information more accurately than scanning the data.

Re: How to find which data sets getting updated daily when you connect to database using SAS libname

ballardw — Mon, 10 Feb 2020 20:30:58 GMT

You may want to define exactly what "getting updated regularly" means.

If you want to know about the data set properties then you might want to look at SASHELP.VTABLE, or the Proc Sql equivalent Dictionary.tables.

One of the variables is CRDATE for creation datetime and another is MODATE for the last datetime it was modified .

For instance if I want to find all of the data sets created on or after a given day such as 09FEB2020:

Proc print data=sashelp.vtable;
   where datepart(crdate) ge '09FEB2020'd and memtype='DATA';
run;

That source has pretty much all of the data set level information that Proc Contents would contain.

Re: How to find which data sets getting updated daily when you connect to database using SAS libname

buddha_d — Mon, 10 Feb 2020 20:45:31 GMT

The mentioned code applies to SAS data sets (sashelp.vtable). The tables are getting created in sql server and I am just accessing them using SAS. So I am trying to see how I can check which tables are updated daily. Thanks Ballard for great suggestion.

Re: How to find which data sets getting updated daily when you connect to database using SAS libname

buddha_d — Mon, 10 Feb 2020 20:47:31 GMT

Thanks Reeza. I would see who could help me. But, is there a way to code each dataset (memname) along with field name (name) to code to see what datasets get updated daily.

Re: How to find which data sets getting updated daily when you connect to database using SAS libname

ballardw — Mon, 10 Feb 2020 20:52:50 GMT

@buddha_d wrote:

The mentioned code applies to SAS data sets (sashelp.vtable). The tables are getting created in sql server and I am just accessing them using SAS. So I am trying to see how I can check which tables are updated daily. Thanks Ballard for great suggestion.

That means you should talk to your SQL Server admin. There should be a similar table or view to access in the SQL Server DB, though it may be better to use passthrough code to access the server reporting tools than to bring all the table properties into SAS.

Re: How to find which data sets getting updated daily when you connect to database using SAS libname

Reeza — Mon, 10 Feb 2020 21:16:20 GMT

Most DBs have a dictionary table type function, you just need to find the right table names.

@buddha_d wrote:

Thanks Reeza. I would see who could help me. But, is there a way to code each dataset (memname) along with field name (name) to code to see what datasets get updated daily.

Re: How to find which data sets getting updated daily when you connect to database using SAS libname

SASKiwi — Mon, 10 Feb 2020 21:43:39 GMT

@buddha_d - It is unlikely that PROC CONTENTS will tell you anything useful about when database tables get updated. I ran a quick test against an SQL Server table and the Date Created and Last Modified columns are blank. Please note these columns are more useful for SAS datasets that are typically re-created from scratch each time.

With database tables rows get inserted, deleted and modified and often there is a timestamp on each row indicating the time it was loaded. Database dictionary tables will likely provide further useful data.

Re: How to find which data sets getting updated daily when you connect to database using SAS libname

Patrick — Tue, 11 Feb 2020 01:37:01 GMT

@buddha_d wrote:

The mentioned code applies to SAS data sets (sashelp.vtable). The tables are getting created in sql server and I am just accessing them using SAS. So I am trying to see how I can check which tables are updated daily. Thanks Ballard for great suggestion.

If you're allowed to query sys.dm_db_index_usage_stats then something like below might return what you're after (code not tested).

LIBNAME ONE SQLSVR DSN=CUSTOMER_DATAMART SCHEMA=DBO;

proc sql;
  connect using one;

  select * 
  from connection to one 
    (
      SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, 
          last_user_update, *
      FROM sys.dm_db_index_usage_stats
      WHERE database_id = DB_ID( 'DBO')
    );

  disconnect from one;
quit;

Re: How to find which data sets getting updated daily when you connect to database using SAS libname

buddha_d — Wed, 12 Feb 2020 04:05:41 GMT

Thanks Patrik for the code. I tested it and I don't have access to sys.dm_db_index_usage_stats . I would look for DBAs to help me.

Thanks all for your suggestions.