Proc SQL and the power of select into
I have referenced this paper many times. Great help in using the power of proc sql with macro variables. Great for building lists of values and dynamic code.
http://www.nesug.org/Proceedings/nesug97/coders/eddlesto.pdf
Syntax:
SELECT
object-item <,objectitem>...
<\ INTO macro-variablespecification <, macro-variablespecification> ...>
FROM from-list ...;
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.
Default value to macro variable
NOTE: This is a great one we picked up from our friends over at the SAS community.
We have run across this literally hundreds of times while programming SAS macros. You need to have a default value for a variable and you don't want to write another macro to set it if it doesn't exist.
This simple fix allows you to check for a value and set a default even in open SAS code.
%global myParameter; /* ensure it exists */ %let myParameter = %sysfunc(coalescec(&myParameter,default-value));
Pure awesomeness..
Credit goes to Don Henderson and the SAS community for this one.
http://www.sascommunity.org/wiki/Tip_of_the_Day:October_12
SAS: Where Also
Ever heard of 'where also'? Neither did we.
We have to give credit to the guys at the SAS Community.
'Where also' allows you to add a series of where statements. The use acts like a single where statement with the and condition.
Example:
Data new_data;
set old_data;
where number=6;
where also another_number=.;
run;
This is the same as where number=6 and another_number=.
SAS DIM function – Counting the elements in an array
The DIM function returns the number of literal elements in an array. It functions against multi-dimensional arrays as well as one-dimensional arrays.
1-dimensional array example
DIM(array_name)
Multi-dimensional array examples
DIM(m_array) -> returns the number of elements in the first dimension of the array
DIM5(m_array) -> returns the number of elements in the 5th dimension of the array
DIM(m_array, 5) -> returns the number of elements in the 5th dimension of the array
The classic use case for the DIM function is to return the number of elements in an array for the upper bound of a do loop process. Example:
array array_name(5) var1 var2 var3 var4 var5;
do i=1 to dim(array_name);
some SAS statements here
end;
SAS LAG funciton
The LAG function is a powerful tool in the SAS programming toolset. It is also one that has unintentional results. First, lets demonstrate how to use it.
data new_ds;
set old_ds;
last_price=lag(price);
run;
The above will return the last value processed for the price variable. The resulting dataset may look like this.
obs price last_price
1 10 .
2 15 10
3 8 15
4 3 8
5 9 3
Now its misuse. Conditional processing will create unexpected results when incorporated with the lag function. Lag returns the last value processed even if it doesn't appear in the resulting dataset. It is also important to note that you shouldn't use lag if you aren't reading dataset records sequentially.
This function can be handy when calculating moving averages and the like.
The following calculates the sum of the last 5 periods:
data new_ds;
set old_ds;
last_5_periods=lag(price)+lag2(price)+lag3(price)+lag4(price)+lag5(price);
ave_5_periods=last_5_periods/5;
run;
SAS arrays ( one dimensional )
A SAS array is a group of variables (or values) under a single name. The grouping is only temporary and only exists for the duration of the data step. There are many ways to declare an array. Additionally, arrays can be modified once they are created.
Here is one example of an array.
data new_ds;
set old_ds;
array test_array{5} test1 test2 test3 test4 test5;
do i=1 to 5;
sum_var+test_array{i};
end;
run;
Here are a few other ways to declare one-dimensional arrays:
Using the {*} designation allows SAS to determine the number of elements in the array.
array test_array{*} test1 test2 test3 test4 test5;
You can also use a range to specify values.
array test_array{5} test1-test5;
The first value doesn't have to be 1. You can specify the values if necessary.
array test_array{10:15} test1-test5;
You can also designate all the variables created into an array. NOTE: you cannot have both character and numeric variables in the same array.
array test_array{*} _NUMERIC_; ( All current numeric variables )
or
array test_array{*} _CHARACTER_; ( All current character variables )
or
array test_array{*} _ALL_; ( All current variables if they are of the same type )
There are also multi-dimensional arrays. We will discuss them in a later post.
If you need help working with arrays. We have an experience team of programmers available for short term help or contract engagements. Don't hesitate to contact us.
Detecting the end of a SAS data set ( end= )
The END= option can help you determine when you have reached the last record in your dataset. Here is a syntax example:
data new_ds;
set old_ds end=end_var;
if end_var=1 then text='This is the last variable';
run;
The end= option creates a temporary variable, in the example it is end_var. This variable is not written to the output dataset. The variable is numeric and has a value of 1 to specify the last record.
Do not use this option with the point option.
This option is helpful when you only want to output the last record (good for summing data).
data new_ds (drop=transactional_record);
set old_ds end=last_record;
sum_var+transactional_record;
if last_record=1;
run;
SAS Do loops – Do While
Here is yet another Do Loop post.
The basic form of a do loop is as follows:
Data new_ds;
Set old_ds;
Do some_index_var= 1 to 50 by 1;
Some SAS statements go here;
End;
run;
However, specifically in this post we want to talk about DO WHILE loops. Do while loops are different because they evaluate the condition at the top of the loop. Example:
Data new_ds;
Set old_ds;
Do while var > 100;
Some SAS statements;
End;
Run;
This loop will not execute the loop if the condition is not met upon the first execution. This is what makes WHILE so valuable. Conditional processes that allows you to exit the loop before the first iteration.
Using SELECT groups for conditional processing
What are Select Groups?
Select Groups are another way SAS enables conditional processing of datasets.
When should we use Select Groups instead of if-then statements?
According to our friends at SAS, you should utilize Select Groups when your conditional criteria is mutually exclusive and numeric. When this is the case, Select Groups are more efficient than if-then processing.
Here are a few examples of conditional processing using Select Groups:
data NEW_DS;
set DS1;
select var1;
when (1) var2='YES';
when (2) var2='NO';
otherwise var2='UNKNOWN';
end;
run;
data NEW_DS;
set DS1;
select;
when (var1=1 and var2='JAN') var3='YES';
when (var1=2 and var2='FEB') var3='NO';
otherwise var3='UNKNOWN';
end;
run;