AFHood Analytics Group – Blogs

NOTE – SAS changed the default settings for fastload in SAS9. This has not been an improvement. SAS has documented how to change the setting back to the previous default in their note here… click here

The fastload facility for Teradata through SAS ACCESS is one that every analyst loading data into Teradata should be aware of. This tool allows you to pump data from SAS to Teradata in the quickest manner. The target table must be empty. There are other limitations as well, but most are uncommon problems.

To use the fastload option, follow the example below (SAS9 and above may require more manual settings to see max performance):

data td_lib.empty_table (bulkload=YES);

set source_table;

run;

Another way…

proc append data=source_table

base=td_lib.empty_table

(bulkload=YES bl_log=append_err_output);

run;

If your organization uses Teradata and could use some help utilizing the full power of SAS with Teradata, we are available to assist you.

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.

One of the handiest commands that I use lately is proc append. The idea behind proc append is that you have two datasets that need to be combined. The datasets should have the same structure and datatypes. Theoretically one dataset would be the ‘master’ or at least the larger of the two.

Instead of merging the datasets or including both in a datastep under the set command, you can use proc append as the most efficient way to add one dataset to another.  Let me try to explain why..

In this example, we are using one of two methods:

data new_dataset;

set dataset_1 dataset_2;

run;

or

data new_dataset;

merge dataset_1 dataset_2;

by some_variable;

run;

Both of these cause the SAS engine to process both datasets creating a new dataset. Even if you call the new dataset by the same name as one of the old datasets, you are still creating a new dataset and then over writing a previous dataset.

The more efficient way to do this if your datasets are set up the same would be the following:

proc append

base=dataset_1

data=dataset_2;

run;

Optionally, you can include the “FORCE” statement after the “data=” command. This tells SAS to ignore the datatypes in dataset_2 as long as the data fits into the types and sizes of dataset_1. Here is the diagram for this code.

Also, this works particularly well when your “base” dataset is a database table referenced via a libname statement. An example would be:

proc append

base=oracle_table.table_1

data=dataset_1

FORCE;

run;

As a precaution, you should always take the time to manually or systematically check to make sure all of your rows were inserted.

So, as you can see, proc append is much more effecient. It creates less processing and less storage for the SAS server. On busy servers, this can really speed up your processing.