SAS Altlog option
If you run a production SAS environment or even a development environment for that matter, it is nice to have a specific location for all logs to be written and managed. ALTLOG helps achieve this.
The ALTLOG option can be set numerous ways, but we typically utilize 2.
1. As an option on the command line. Example:
sas sasprogram -altlog /my/log/dir/sasprogram_$timstamp\.log
2. Globally in the sas configuration file. This is wonderful as an administrator. All of the logs drop into one location for easy cleanup or archival. Users can specify an alternate location if they don't want it in the default, so everyone wins.
SAS / Teradata Fastexport – dbsliceparm = all
Fastexport is the fastest way to get large data out of teradata. Fastexport utilizes multiple connections to deliver data and therefore speeding up the transfer of data between Teradata and SAS.
Here are a few examples of fastexport.
/* libname statement*/
libname teradb teradata username=&un password=&pw dbsliceparm=all;
/* explicit sql */
proc sql;
connect to teradata(username=&un password=&pw dbsliceparm=all);
<SQL STATMENT>
quit;run;
How do you know if fastexport was used?
Use this option:
options sastrace=',,,d' sastraceloc=saslog;
If it is working, you should see something in your log like:
Select was processed with fastexport.
There are many other factors that come into play if fastexport doesn't work. Check the requirements on the SAS support page for troubleshooting.
Let SAS check for a database connection
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 History2009.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;
SAS Proc datasets – What can’t it do?
Of all the SAS procedures, this must be one of the most flexible. Any thing related the the dataset as an entity can be done with the Proc Datasets procedure. The basic syntax is as follows:
proc datasets <options>;
<commands> <options> ;
run;
Here are only a few of the thing you can accomplish with Proc Datasets.
Copy datasets in batch or singularly. Very similar to the proc copy syntax.
proc datasets ;
copy out=target_library in=source_library ;
select dataset_list ; /* This statement is optional and only if
you want to specify the datasets to be copied. Omitting this
will copy all datasets in the source_library. */
run;
Delete a dataset. See more examples here.
proc datasets library=lib_name;
delete dataset_name;
run;
Delete all datasets in a library.
proc datasets library=lib_name kill;
run;
Rename datasets.
proc datasets library=lib_name;
change old_dataset = new_dataset;
run;
Delete all the labels in a dataset.
proc datasets library=lib_name;
modify dataset_name;
attrib _all_ label=''; /* change all to the variable name for a specific variable */
run;
Append a dataset
proc datasets library=lib_name;
append out=destination_dataset data=source_dataset;
run;
Some important options to use on the proc datasets procedure include the nolist option that suppresses the list of datasets in the library. This can become very annoying if not used. SAS will fill your log with list of datasets if you are not using the nolist option. Also, force is used often when appending data. This will force the append to occur even if the datatypes aren't matches. Memtype can be used as an option to limit the files in the library for which you will be operating. This is particularly useful when deleting datasets.
If you need assistance with you SAS programs, please give us a call. We have a dedicated team of programmers ready to tackle your problems.
SAS Temporary Variables – Just drop them..
This is a great practice that we picked up from the guys over on SAS-L. It is a way to create temporary variables in datasteps that are dropped before being written to the output dataset.
data output_dataset (drop=_:);
set input_dataset;
_temp_var = <some code here>;
new_variable = <some more code here.. probably something to do with the _temp_var>;
run;
The variables beginning with the underscore ("_") will be dropped before being written to the output dataset. It doesn't matter if there is one temp variable or 1000. As long as they begin with an underscore, they will not make it to the output dataset.
If you use this drop statement without creating any temporary variables you will get a warning.
Check to see if a dataset exists
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
Logging Your Time and Code
As a consultant or even an employee, sometimes it is hard to explain what you have been working on. I know that I have gotten that dreaded question many times. "What have you been working on?" or "what have you been doing all week?" Often I hear myself answering with the truth that is less than impressive. To help me prove my value and prove when and what I'm working on, I built a logging macro to help explain my work.
The my version of this logger utilizes a database for storage, however you could easily use a SAS dataset instead.
%macro mylog;
/* ------------------------------------------------------TITLE - MyLog
AUTHOR - Adam Hood (www.afhood.com)
This macro logs your execution of SAS scripts. You can
call this macro at the beginning and optionally at the
end. The following variables can be set by the user or
they will be filled in by the macro (ALL VARIABLES ARE
OPTIONAL):variable - description
proj_name - Name of overall project (may have many code
names associated with it)
proj_nbr - Number associated with a given project. This
is often used to track projects by project managers.
code_name - Name of the code you are current executing.
This can be part of an over-riding project.
comment - Any comments you want to record. If no
comment is set the macro will record 'code invoked'.------------------------------------------------------*/
%global code_name;
%global proj_name;
%global proj_nbr;
%global comment;
%global code_start;/* Set code_name variable */
%if %bquote(&code_name) eq
%then %do;
%let code_name =Adhoc code (name not set);
%end;/* Get system user name */
%let analyst = &SYSUSERID;/* Check for a project name or number */
%if %bquote(&proj_name) =
%then %do;
%let project_name = Project number: ;
%end;
%else %do;
%let project_name = &proj_name :;
%end;%if %nrbquote(&proj_nbr) =
%then %do;
%let project_name = &project_name 0;
%end;
%else %do;
%let project_name = &project_name &proj_nbr ;
%end;/* Set comments area if not set*/
%if %bquote(&comment) =
%then %do;
%if %nrbquote(&code_start) = %str() or &code_start =0
%then %do;
%let comment = Code invoked;
%let code_start = 1;
%end;
%else %do;
%let comment = Code complete;
%let code_start = 0;
%end;
%end;/* Setting the timestamp */
/* NOTE if the timestamp is already set automatically in the database (via
a trigger or the like) then you should comment out this section */%let timestamp = datetime();
%let timestamp_statement = , timestamp=×tamp ;/* Inserting into table or dataset */
proc sql;
insert into work.mylog set analyst="&analyst", project_name="&project_name",
code_name="&code_name", comments="&comment" ×tamp_statement;
quit;
%mend mylog ;/* Set the following OPTIONAL variables when running your script */
%let proj_name =;
%let proj_nbr =;
%let code_name =;
%let comment =;%mylog;
proc print data=work.mylog;
run;
I hope this helps all of you keep track of what you are working on. I keep this macro in my setup file that runs with every SAS execution. I also use it in my automated SAS jobs to help me know when they ran.
Additionally, we also have a more sophisticated version that is customized to help us know when jobs run and if they are successful. Feel free to contact us to help you setup your automated SAS system with logging and error reporting.