Tag Archives: SAS

This post is for people, like myself, who assume things are harder than they usually are. Worked on an analysis yesterday where I needed to map an Access database to a SAS library so I could use the tables in some joins. My first thought was ODBC. I proceeded to setup the connection, only to find out my company didn’t give everyone that ability. Bummer. 15 minutes wasted.

Searched around and couldn’t find any other ideas. Then I thought, I’m going to try to just reference the database directly in the LIBNAME statement and see what happens. It worked. So, this post is to save people who, like myself, usually try to hard way first, only to find their way back to simplicity.

Example:
LIBNAME TEST "C:\test\database.accdb";

Nobody who reads this blog is likely a SAS programmer. However, on the off chance this would be useful to someone who finds it via Google, I wanted to post.

Currently, I am working on a project in SAS Enterprise Guide where I needed to allow the user to provide variable value(s) via prompt manager and then filter the resulting dataset based on the user’s input. After rubbing a few brain cells together, created a macro that does the trick. It works by embedding a loop inside of a WHERE statement in SQL. If the user selects one value the loop ends and returns that value into the WHERE statement. However, if multiple values are selected, the loop iterates until all values have been returned.

There’s probably a better way, but at least this works. Here’s a quick outline of the variables.

PRODUCTCATEGORY: This is the name of the prompt variable I setup. Replace it with your variable of choice.
IN: Input dataset
OUT: Output dataset
FIELD: Variable in the dataset on which the filtering is performed.


%MACRO PROCESS(IN,OUT,FIELD);
PROC SQL; CREATE TABLE &OUT. AS SELECT * FROM &IN.
WHERE &FIELD. IN (
%IF &PRODUCTCATEGORY_COUNT = 1 %THEN
"&PRODUCTCATEGORY";
%ELSE %DO I=1 %TO &PRODUCTCATEGORY_COUNT;
"&&PRODUCTCATEGORY&I"
%END;
);
QUIT;
%MEND;


For Part II in this series of posts on DO Loops in SAS, I wanted to discuss an application slightly more complex than the previous example. Loops can not only be used to perform data correction (as shown in the previous post), but can also be very useful in creating new records where none exist.

We’ll discuss using order data again as our example.

Your client would like you to create a daily trend report showing YTD Quantity Sold by Product by Date. In our made-up table, let’s say for simplicity there are four fields: Order Number, Order Date, Product and Quantity. This fictitious company sells two products (Widgets and Super-Widgets) and follows a calendar fiscal year. The data might look something like this.

You probably noticed, in its current state, the table above cannot generate this report. We don’t have an appropriate date field and quantities aren’t cumulative. The good news is that, utilizing a loop, with a few lines of code we can create a table that will serve our purpose. In order to generate the requested report, we need to create a table that looks like this. Notice that quantities are cumulative and we have a one record for each product every day.

So how do we get from Point A to Point B? Good question. There are two steps. Let’s dive in.

STEP #1: It’s loop time.

Using a combination of a DO UNTIL loop and the OUTPUT statement, we can generate a new order record for every date that each order existed within the fiscal year. “What?” You might say. Take the first order (2135468). That order was placed on 1/2/09, which means it should be included in the cumulative quantity from 1/2/09 – 12/31/09. Therefore, we need to create 363 new records for that order which contain the dates 1/3 – 12/31, so when we summarize that quantity is included in the total. This can be accomplished by using the following code.

*Fiscal Year start date;
%LET BEGDTE = ‘01JAN2009’D;

*Date of current reporting;
%LET RPTDTE = ‘04JAN2009’D;

DATA TEST_2;
	SET TEST_1;
	RUNDATE = &RPTDTE.;
	DO WHILE (RUNDATE GE &BEGDTE.);
		IF ORDERDATE le RUNDATE;
		OUTPUT;
		RUNDATE = RUNDATE - 1;
	END;
RUN;

This will create a table that contains an individual record for every order on every date that order existed between the reporting date and the order date. If you have a large orders table this step could result in millions of records. Literally.

STEP #2: Summarize the data.
After that we can use a simple PROC SUMMARY to summarize the data and we have successfully moved from Point A to Point B.

PROC SUMMARY DATA=TEST_2 NWAY MISSING;
        CLASS RUNDATE PRODUCT;
	VAR QUANTITY;
        OUTPUT OUT=TEST_3 (DROP=_TYPE_ _FREQ_) SUM=;
RUN;

We can now generate the requested trend report and the client is happy. What more could you ask.

DO Loops in SAS are outside the scope of what most beginning users feel comfortable tackling. However, they are a powerful, time-saving tool in the report writer’s toolbox. For those unfamiliar with programming, loops are a set of instructions that the programmer tells the computer to repeat until predefined condition(s) are true. It’s a simple concept but powerful in execution.

To illustrate, here is an example of where a loop might be used. Say, you have a table that contains order data. This particular company has decided that its Order Number is seven digits long and starts with “0000000”. The next order in sequence would be “0000001” and so forth. Well, the person who sent you the data decided it would be a good idea to put it into Microsoft Excel, which then reads the Order Number field as numeric and promptly removes all the leading zeros.

Perfect. (This has never happened to me…can you tell?) However, with three lines of code a DO Loop can easily correct this issue.

Here we go.

In SAS, there are two types of loops, DO WHILE and DO UNTIL. The main difference is when the logical condition is evaluated.

DO WHILE
The condition is evaluated at the top of the loop before the statements within the DO loop are executed. If the expression is true, the DO loop iterates. If the expression is false the first time it is evaluated, the DO loop does not iterate. Not even once.

DO UNTIL
The condition is evaluated at the bottom of the loop after the statements in the DO loop have been executed. If the expression is true, the DO loop does not iterate again. The DO loop always iterates at least once.

So, using our example above we want to use a DO WHILE loop, which will evaluate the condition before execution and then run if the condition is met. Here is our code. The loop statement is italicized in the middle of the data step.

DATA TEST_2;
   SET TEST_1;
      DO WHILE (LENGTH(COMPRESS(OrderNumber)) LT 7);
         OrderNumber="0"||OrderNumber;
      END;
RUN;

This code is performing the following steps.

1. Reads in the first record.
2. DO Loop looks at the Order Number field. If the length of the number is less than seven digits, then a “0” is appended to the front.
3. DO Loop checks the condition again. If the length is still less than seven digits, the loop iterates again. If not, the loop ends, the next record is read-in, and the process repeats itself.

This example gives you an idea of how DO Loops might be used, but their application can go far beyond simple data correction. Until next time. Happy coding.