AFHood Analytics Group – Blogs

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 number=.;

run;

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;

Have you ever wanted to know when your code completes? Or maybe you want to automate the report to include sending an email?

Regardless, SAS is happy to send that email for you. The easiest way to do this is through the SMTP access method via filename statements.

Example:

filename sendemail email ‘toaddress@email.com’ subject=’This is a test email.’ from=’fromaddress@email.com’;

data _null_;

file sendemail;

put ‘Hi,’;

put ‘This is a email sent from SAS’;

run;

This is only a simple example. Here is another with an attachment.

filename sendemail email ‘toaddress@email.com’ subject=’This is a test email.’ from=’fromaddress@email.com’ attach=’/somesascode.sas’;

data _null_;

file sendemail;

put ‘Hi,’;

put ‘This is a email sent from SAS’;

run;

Email attributes can also be set in the data step through the EM_ directives.

filename sendemail email ‘toaddress@email.com’ ;

data _null_;

set somedataset;

put ‘!EM_TO! ‘ email_addr;

put ‘!EM_SUBJECT! ‘ subscription_name;

put name ‘ ,’;

put ‘This message was generated by a SAS data set’;

put ‘!EM_SEND!’;

run;

As any good analyst knows, working with data is not the problem. Good analysts have the skill necessary to combine, split, merge, slice, aggregate, or summarize data any way you can imagine. However, is that enough?

Probably not..

What good does data do if you can’t communicate its meaning in a logical way. The day of sending a spreadsheet with pivot tables are heading out the door. Today users are wanting more. They are expecting more. And why is that?

Here are a few reasons users expect more from their data experiences:

• Google Analytics
• Twitter
• IPhone
• ITunes
• Amazon

These are just some of the companies leading the way in user interface. The days of cheezy HTML or excel graphs won’t cut it. Users quickly see they have to work to understand the information and move on.

The solution?

A complete toolbox of data tools and a team of professionals with tool experience. Today’s environment requires (along with some of our favorite vendors/partners):

• a storage solution
  • Teradata
  • Oracle
  • MySQL
• an analysis tool
  • SAS
  • R Project
  • in-database programming (PL/SQL)
• a display layer
  • Data Applied
  • Custom solutions (Juice Analytics)

If your organization isn’t firing on all analytic cylinders, let us know. We have the tools, relationships, and people to move you to the next level. We have a proven track record of moving the needle through the integration of data into decision making.

We can’t stress the importance handling character case correctly. Here are the two functions you need to know and use correctly.

In order to convert all characters in a sting to lowercase, use the LOWCASE function. Example:

data ds_2;

set ds_1;

*convert it to lowercase;

new_char_var=lowcase(OLD_CHAR_VAR);

run;

In order to convert all characters in a sting to uppercase, use the UPCASE function. Example:

data ds_2;

set ds_1;

*convert it to uppercase;

NEW_CHAR_VAR=upcase(old_char_var);

run;

We can’t stress the importance of keeping this functionality in mind. Many a SAS script has gone awry because a developer didn’t take character case into account when using conditional or search statements.

TIP: A solid programming practice is to convert everything to lowercase before storing it. There are exceptions to this (like names, etc), but they can be handled on a case basis.

Also note the PROPCASE function. It capitalizes the first letter of each word in a string. Example:

data ds_2;

set ds_1;

New_Prop_Var=propcase(old_char_var);

put New_Prop_Var;

run;

This Is The New Prop Var.

The INTNX function increments dates, times, or datetimes by specific or custom intervals.

Here is the basic syntax for INTNX:

INTNX(interval[multiple.shift-index], start, increment[, alignment])

The interval can be a set value like – DAY, WEEK, DTWEEK, YEAR, etc. You can also specify a custom interval. We will post more on that later.

The multiple is optional and used as such. If you want the interval to be a 2 year period or biennial, you could use YEAR2. It is the same with weeks. If you were calculating 2 week pay periods and you wanted to increment by such, you would declare the interval to be WEEK2.

The shift-index lets you define a starting point within the interval. An example would be to start the week on Monday.  The interval designation should be WEEK1.2 .

The start is your date, time, or datetime at which you want to begin the increment.

Increment is a positive or negative integer representing the number of interval shifts that should be made from the start.

Alignment is an excellent tool. It allows you to designate at which point SAS should return within an interval. You can specify:

B – beginning

M – middle

E – end

S – same

The default for alignment is beginning.

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;

The SAS DIF function is another useful tool for operating on previous records.

Lets say you want to know the difference in the last year’s revenue and this year’s revenue. Here is the data:

Year          Revenue

2001           10,000

2002          30,000

2003          60,000

2004          100,000

data revenue1;

set revenue;

Rev_increase=dif1(revenue);

run;

Year          Revenue         Rev_increase

2001           10,000         .

2002          30,000         20,000

2003          60,000         30,000

2004          100,000       40,000

The dif functions compares the current record to the nth previous record.

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;

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.