topic Re: Program to extract data from multiple datasets in SAS Programming

Program to extract data from multiple datasets

Babloo — Thu, 06 Dec 2018 13:45:29 GMT

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

Re: Program to extract data from multiple datasets

Kurt_Bremser — Thu, 06 Dec 2018 13:57:12 GMT

Retrieve the information from dictionary.tables (or sashelp.vtable).

Re: Program to extract data from multiple datasets

Babloo — Thu, 06 Dec 2018 14:01:39 GMT

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.

Re: Program to extract data from multiple datasets

RW9 — Thu, 06 Dec 2018 14:47:14 GMT

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.

Re: Program to extract data from multiple datasets

Reeza — Thu, 06 Dec 2018 16:29:35 GMT

@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.

Re: Program to extract data from multiple datasets

ballardw — Thu, 06 Dec 2018 17:03:37 GMT

@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.

Re: Program to extract data from multiple datasets

Jagadishkatam — Thu, 06 Dec 2018 17:18:07 GMT

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;

Re: Program to extract data from multiple datasets

Babloo — Fri, 07 Dec 2018 04:55:56 GMT

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	.	.	.

Re: Program to extract data from multiple datasets

Babloo — Fri, 07 Dec 2018 05:01:17 GMT

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;

Re: Program to extract data from multiple datasets

Reeza — Fri, 07 Dec 2018 05:57:47 GMT

That’s because you don’t have SAS datasets. You have data on a server which is very very different. Post on the appropriate server forum instead.

Re: Program to extract data from multiple datasets

RW9 — Fri, 07 Dec 2018 09:16:20 GMT

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;