I have a data set of sales data by day. Unfortunately the names of the columns represent the dates. In order to work with the data, I need to transform the data set so each day represents an observation.

The data set looks something like this:

store _010_05_01 _010_05_02 _010_05_03 ....
1     8          5          6
2     6          9          3
3     7          9          8
....

As you can see, I have a store variable and several variables that are named by the date and contain the number of sales for that day. I need it to look like this:

store date   sales
1     5/1/10 8
1     5/2/10 5
1     5/3/10 6
2     5/1/10 6
2     5/2/10 9
2     5/3/10 3
....

Unfortunately this is a pretty common problem when you receive data from vendors who aren't sure how you are going to work with it. Luckily the transformation is pretty easy:

1) data byDay;
2)   set myData;
3)   array t[*] $ _010: ;
4)   do i = 1 to dim(t);
5)     sales = t[i];
6)     date  = vName( t[i] );
7)     output;
8)   end;
9) run;

This little data step creates an array to hold our date variables. (3)We use a little syntax sugar to keep from having to type out all the variable names _010:. The colon tells the SAS compiler to list out all the variables that have the _010 prefix. (4)Then we loop through all the elements in our t[] array. We use the vName() function to get the name of the variable that t[] is referring to (6). And finally we output once for each iteration of our loop (7).

Obviously if your date columns aren't named uniformly (_010:) then things won't work as nicely. And you would likely want to add a KEEP statement to just keep the variables you are creating along with any others you might be interested in.

If you made it this far, you may have noticed I am not quite finished with my transformation. Line (6) assigns the value of date to something like '_010_05_01'. Not quite what we wanted. We need to turn that into a SAS date. I need to substr() the first two characters off to remove them (_0) and then input() the result using the appropriate informat. So line (6) should really be:

6) date = input( substr( vName( t[i] ), 3), yymmdd8. );

A little more complicated, but hopefully still understandable.

Hopefully you find this useful! Comment with any comments/observations.

Finding the Max Value In An Array

The max() function makes it easy to find the maximum value in a SAS array.

Given an array like:

array x[*] x1-x10;

maxValue = max(of x[*]);

Pretty slick, eh? Remember, it doesn't return the position of the max element, just the max value.

This can be pretty useful if you want to find out if at least one element of an array has a value. Like if you have an array of answers and you want to find out if there is at least one answer in the array.

hasAnAnswer = max(of answers[*]);

or in logic:

if max(of answers[*])
 then do;
        * yes, they have answered at least one question;
      end;

*NOTE: I have decided to keep this SAS programming blog SAS focused and move my Ruby on Rails writing to a Rails oriented blog. I am a guest contributor at www.HABTMProjects.com. If you were interested in following along with my RoR projects then please hop on over there and sign-up.*

Where Did the Observation Come From?

Here is a little snippet of code I created to address the problem of assigning a value to a variable based on what data set an observation came from in a data step. Here is an example:

Suppose I have a whole bunch of data sets each representing a different country. I want to set a lot of them in one data step and create one resulting data set with a variable called language. In order to create the language variable correctly, we need to know which data set the observation is coming from. Typically we would use the IN= option on the data set to create a flag and then check that flag using if/then logic.

data selectedCountries;
  set
    chile(in=chile)
    china(in=china)
    costa_rica(in=costa)
    egypt(in=egypt)
    fiji(in=fiji)
    turkey(in=turkey)
    usa(in=usa)
    saudi_arabia(in=saudi)
  ;

  if chile then language = 'SPANISH';
  else if china then language = 'CHINESE';
  else if costa then language = 'SPANISH';
  etc etc etc...
run;

One of the major problems with this approach is it does not scale well. The more countries you set, the more problematic your if/then logic becomes.

Here is a slightly more elegant solution that uses arrays and variable information functions. You still use the IN= option on the data set, however you want to name the in= variable the same as the value we want to assign. Then you create an array of all those in=variables. Finally, you loop through the array of in= variables and check for their boolean value. If it is true then you assign your new variable the value derived from the vname() function.

data selectedCountries;
  set
    chile(in= SPANISH)
    china(in= CHINESE)
    costa_rica(in= SPANISH)
    egypt(in= ARABIC)
    fiji(in= ENGLISH)
    turkey(in= TURKISH)
    usa(in= ENGLISH)
    saudi_arabia(in= ARABIC)
  ;
  array names[*] SPANISH CHINESE ARABIC ENGLISH TURKISH;
  do i = 1 to dim(names);
    if names[i] eq 1
     then language = vname( names[i] );
  end;
run;