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
“call symputX” is the symput upgrade
SymputX is the upgrade from Call Symput.
Syntax:
call symputx("macro_var_name", character_value (or numeric to be converted to char), symbol table def);
Where symput will produce a note about converting character values to numeric, symputx won't produce such a note. Additionally it will strip leading and trailing spaces form the character value. Lastly, you can define which symbol table should be used (G=global, L=local, F=operates like symput).
data test;
set test_old;
call symput('variable_name', 'numeric value') /* gets a warning although it runs successfully*/;
call symputx('xvariable_name', 'numeric value') /* no warning for numeric conversion*/;
run;
SAS Do Loop – Do Until
Do loops are great for a long list of tasks. Understanding do loops can save you an innumerable amount of programming and work arounds.
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 UNTIL loops. Do until loops are different because they evaluate the condition at the bottom of the loop. Example:
Data new_ds;
Set old_ds;
Do until var > 100;
Some SAS statements;
End;
Run;
This loop will execute the SAS statements at least one time and evaluate the var>100 condition at the bottom of the loop. If the condition is false, the loop will execute again.
SAS fastload to Teradata
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.
Managing database connections with SAS
Here is one for the books.
When writing dynamic code or code that spawns other scripts, it may be important to think about how many database connections you are using. There are a couple of ways to insure you are a nice consumer of the database resources. One way that doesn't require any management would be to limit your connections with connection=global options.
Proc sql;
connect to teradata(user=user1 pw=XXXX server=myserv database=mydb connection=global);
<some sql statements here>
disconnect from teradata;
quit;
If you use multiple pass-through or pass-through and libname statements that all have the same connection values and have a connection=global option, SAS will not open subsequent connections to the database on each call. However, if you are accessing a difference database, schema, user, etc, SAS will need to make a new connection. There are many other connection parameters that are similar that should be explored if this is important to you or your client.
Here are a few:
CONNECTION_GROUP
DBCONINIT
DBPROMPT
DEFER (This is another good one for limiting your database activity for large, long running scripts)
We will also explore the world of checking for database connections. This is important for large scripting efforts. It will prevent your code from dying simply because there are no connections available. See more here.
Load a SAS macro variable via call symput
Very often you need to load your macro variable within a data step. To do so, you can use the CALL SYMPUT command. See the example below.
Data test;
set test1;
call symput( 'macrovar' , dataset_variable );
run;
%put ¯ovar;
If you have any questions about this or any other SAS functions feel free to contact us.
How to make SAS quit
When writing SAS macros and scripts, sometimes you need to tell SAS to exit or quit. We often use this when we are error handling. Here is a short example.
%macro test;
%let code_error=0;
< some SAS code >
%if &code_error=1%then %goto exit;
%mend test;
Let us know if we can help you with your SAS code or analytics project.