AFHood Analytics Group – Blogs

Here is a great macro that you can be use to check for database connections. The macro will attempt to connect to the database and return a value when complete signifying success or failure.

You can specify the variables via macro parameters or filling in the details within the macro.

%macro check_db_connect(max_connection_attempts,connect_to_string,time_to_sleep);

%local sleep_statement connected_to_database;
/*——————————————————————–
Program Name : check_db_connect_ah.sas
Purpose      : checks database connection
Author       : HoodA (www.AFHood.com)
Arguments
You may pass the values in via the macro parameters or by setting the
values within the macro itself.

Requirements
Only checks one database connection. Must run multiple times for each
database connection you want to check.

Returned values
This macro will return a variable, “database_connection” with
a value of YES or NO;

===================================================================
Change History

2009.03.01 – hooda – Macro created

———————————————————————*/

/* Settings */
%* USE THESE SETTINGS IF YOU DONT PASS THE VARIABLES INTO THE MACRO
VIA THE MACRO PARAMETERS ;
%let max_connection_attempts_default=10;
%let connect_to_string_default=oracle(user=xxxx orapw=xxxx path=xxxx ); /* change this to meet your needs */
%let time_to_sleep_default=10; /* number of minutes to wait between connection attempts */

%put &connect_to_string_default;
%put &connect_to_string;
%put %length(&connect_to_string);
/* Settings */

%* checking for passed parameters ;
%if (&max_connection_attempts =”" or &max_connection_attempts le 1 ) %then %do;
%let max_connection_attempts=&max_connection_attempts_default;
%end;
%if ( %length(&connect_to_string) < 5 ) %then %do;
%let connect_to_string=&connect_to_string_default;
%end;
%if ( &time_to_sleep le 1 ) %then %do;
%let time_to_sleep=&time_to_sleep_default;
%end;

%* this is the actual sleep statement ;
%let sleep_statement=%eval(&time_to_sleep*60);

%put Attempting to connect to Database;
%put Using string: &connect_to_string;
%put Trying to connect a maximum of &max_connection_attempts times;
%put waiting &time_to_sleep minutes ( or &sleep_statement seconds );
%put between attempts.;
%put ;

%let connected_to_database=NO;

%* attempt to connect up to x times at x minute intervals,;
%* until successful or out of time;
%do i = 1 %to &max_connection_attempts;
proc sql;
connect to &connect_to_string;
%* if the query succeeded, exit the loop ;
%if ( &SQLXRC=0 ) %then %do;
%let connected_to_database=YES;
%if ( &i = 1 ) %then %do;
%put Connection succeeded after 1 attempt.;
%end;
%else %do;
%put Connection succeeded after &i attempts.;
%end;
%let i=&max_connection_attempts;
%end;
%else %do;
%* if the connection failed, wait 10 minutes and try again;
quit;
filename sleepout pipe “sleep &sleep_statement”;
data sleepout;
infile sleepout;
run;
%end;
%end;
%* connection should have been made or reached max attempts;

%* we will return a result to the program, ;
%* YES or NO as a value of database_connection. ;
%global database_connection;
%if &connected_to_database=YES %then %do;
disconnect from oracle;
quit;
%let database_connection=YES;
%end;
%else %do;
%put Connection failed after &max_connection_attempts attempts.;
%let database_connection=NO;
%end;
%mend check_db_connect;

There are times when we find ourselves needing to move many datasets within a library to another location. One of many ways to do this is with the Proc Copy procedure.

The elements of a Proc Copy procedure are as follows:

proc copy in=source_library out=target_library memtype=all;

select dataset1 dataset2 dataset3;

run;

The memtype option allows you to specify what kind of member you want to move. This is optional and if you leave it off, SAS will default to ALL. Here are you available choices for this option.

ACCESS – access descriptor files (created by SAS/ACCESS software)

ALL – all member types

CATALOG – SAS catalogs

DATA – SAS data sets

FDB – financial database

MDDB – multidimensional database

PROGRAM – stored compiled SAS programs

VIEW – SAS data views

Additionally, the select statement can be left off as well. Doing so will select all datasets that meet the memtype requirements. It might be worth noting that all datasets copied will have a timestamp equal to the runtime of the procedure not equal to the source dataset.

If you have data management challenges, give us a call. We will help you design a solution that meets your needs.

Deleting datasets is something only seasoned coders tend to do. The use of temporary datasets outside of the work library require thoughtful use of code.

It is often that you must check to see if datasets or database tables already exists in an automated fashion. If they do, you need to delete them or archive them before creating a new version. There are many ways to delete a dataset. The easiest and most straightforward is the “proc datasets” command.

proc datasets library=libname_of_dataset;

delete dataset_name;

run;

This will remove the dataset if it exists. If it does not exist, you will get a message and the program will continue. I recommend issuing an “IF” command to check the existence of a dataset instead of simply deleting one that might exist.

Later I will provide you with a macro that deletes datasets if they exist.

One of the checks we do many times in automated jobs is to constantly check to see if files and datasets are where we expect them to be. One of the easiest ways to do this is the macro language. We can use an IF statement with a system function. See below..

/*see if file exists*/

%if %sysfunc(exist(libname.dataset1)) %then %do ;

/*if it is here then*/

proc sql;
select some_data from some_table;
quit;

%end;

%else %do;
/*if the file does not exist then*/

%put WARNING: The file does not exist! ;

%goto exit;

%end;

To review, the above macro snippet checks to see if the dataset is there. If the dataset is there, then it runs a proc sql command. If it is not, it returns a WARNING and then exits.

Let us know if you a project that needs consultation or contract work.

www.AFHood.com