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));
Credit goes to Don Henderson and the SAS community for this one.
SymputX is the upgrade from Call Symput.
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).
call symput('variable_name', 'numeric value') /* gets a warning although it runs successfully*/;
call symputx('xvariable_name', 'numeric value') /* no warning for numeric conversion*/;
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:
Do some_index_var= 1 to 50 by 1;
Some SAS statements go here;
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:
Do until var > 100;
Some SAS statements;
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.
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);
proc append data=source_table
If your organization uses Teradata and could use some help utilizing the full power of SAS with Teradata, we are available to assist you.
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.
connect to teradata(user=user1 pw=XXXX server=myserv database=mydb connection=global);
<some sql statements here>
disconnect from teradata;
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:
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.
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.
call symput( 'macrovar' , dataset_variable );
If you have any questions about this or any other SAS functions feel free to contact us.
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.
< some SAS code >
%if &code_error=1%then %goto exit;
Let us know if we can help you with your SAS code or analytics project.