SAS tips

7月 112011
 

SAS Enterprise Guide sets values for several useful SAS macro variables when it connects to a SAS session, including one macro variable, &_CLIENTPROJECTPATH, that contains the name and path of the current SAS Enterprise Guide project file.

(To learn about this and other macro variables that SAS Enterprise Guide assigns, look in Help->SAS Enterprise Guide help, and search the Index for "macro variables".)

If your SAS program knows the path of your project file, then you can use that information to make your project more "portable", and reference data resources using paths that are relative to the project location, instead of having to hard-code absolute paths into your program.

For example, I've been working with some data from DonorsChoose.org, and I've captured that work in a project file. After saving the project, I can easily get the name of the project file by checking the value of &_CLIENTPROJECTPATH:

15    %put &_clientprojectpath;
'C:\DataSources\DonorsChoose\DonorsChoose.egp'

If you can get the full path of the project file, then you can build that information into a SAS program so that as you move the project file and its "assets" (such as data), you don't need to make changes to the project to accommodate a new project folder "home". Here is the bit of code that takes the project file location and distills a path from it, and then uses it to assign a path-based SAS library.
/* a bit of code to detect the local project path                          */
/* NOTE: &_CLIENTPROJECTPATH is set only if you saved the project already! */
%let localProjectPath =
%sysfunc(substr(%sysfunc(dequote(&_CLIENTPROJECTPATH)), 1,
%sysfunc(findc(%sysfunc(dequote(&_CLIENTPROJECTPATH)), %str(/), -255 )))); 

libname DC "&localProjectPath";

Here's the output:
21  libname DC "&localProjectPath";
NOTE: Libref DC was successfully assigned as follows:
Engine:        V9
Physical Name: C:\DataSources\DonorsChoose

This allows me to keep the project together with the data that it consumes and creates. And I can easily share the project and data with a colleague, who can store it in a different folder on his/her machine, and it should work just the same without any changes.

7月 082011
 
As I write this, SAS 9.3 has not yet been "shipped", but its release is imminent. I've already heard many questions about how SAS Enterprise Guide works with the new version, so I decided to write this "Frequently-soon-to-be-asked questions" document to help sort it out.

What version of SAS Enterprise Guide comes with SAS 9.3?
Initially, you'll get SAS Enterprise Guide 4.3 (known sometimes by its "deployed version" as 4.305). We call this a "remastered" version, because it already contains all of the hotfixes that have been made available since the initial release in August 2010.

Does this "remastered" version include any new features?
No, there are no new features beyond what's already in the 4.3 release. The development team is working on a new release, tentatively called "SAS Enterprise Guide 5.1", which contains many new features including a few that take advantage of new SAS 9.3 capabilities. We previewed some of these new features at SAS Global Forum 2011.

Ooh, cool! When will that 5.1 thing be released?
Do you really think that I'm going to comment on software ship schedules on this blog? Come on, you should know better than that.

Well, I had to ask. So what versions of SAS Enterprise Guide will work with SAS 9.3?
You'll need SAS Enterprise Guide 4.3 to connect to a SAS 9.3 environment. From a purely technical standpoint, you can continue to use SAS Enterprise Guide 4.1 and 4.2 with a locally installed version of SAS on your PC, even SAS 9.3. (That's local SAS only, with no SAS metadata environment.) Of course, we recommend SAS Enterprise Guide 4.3 for the best experience. And if you call SAS Technical Support with questions about using an earlier version of SAS Enterprise Guide with SAS 9.3, the first response will probably be "use the 4.3 version."

Can I use the same SAS Enterprise Guide 4.3 to connect to SAS 9.2 and SAS 9.3?
Yes. And when SAS Enterprise Guide 5.1 is released, it will also work with both SAS 9.2 and SAS 9.3.

I have SAS Enterprise Guide 4.3 installed on my desktop already, working with SAS 9.2. Do I need to install a newer version to connect to SAS 9.3?
No, there is no need to reinstall the desktop client application. However, we recommend that you apply any available hotfixes for SAS Enterprise Guide before you connect to SAS 9.3. There are a few compatibility issues that have been fixed along the way. Again, if you install the version of SAS Enterprise Guide 4.3 that ships with SAS 9.3, those fixes are already applied.

What happens to my current SAS Enterprise Guide if I install the SAS 9.3 package on the same desktop machine?
Okay, stay with me here... When you install SAS 9.3 with its remastered version of SAS Enterprise Guide, and if you already have SAS Enterprise Guide 4.3 installed, there is a bit of magic involved. Magical deployment fairies will sprinkle the new SAS Enterprise Guide version into the SAS 9.3 "home" directory, take a peek at your "old" SAS Enterprise Guide for any recent content (custom tasks, more recent hotfixes) and move that content into the new location, and then remove (uninstall) the old version of SAS Enterprise Guide.

When it's over, you shouldn't notice any difference in how things work.

What about the other client applications? How do they work with SAS 9.3?
Here's a quick summary:

  • SAS Add-In For Microsoft Office 4.3 is included with SAS 9.3 BI as a remastered version, just like SAS Enterprise Guide 4.3. It works with SAS 9.2 and SAS 9.3 as I've described here.
  • SAS Management Console 9.3 ships with SAS 9.3. It works only with SAS 9.3. You can continue to use the 9.2 version for SAS 9.2.
  • SAS Information Map Studio 4.31 ships with SAS 9.3. It works only with SAS 9.3. You can continue to use the 4.2 version for SAS 9.2.
  • The BI web clients such as SAS Web Report Studio and SAS BI Dashboard also have a 4.31 version. They work only with SAS 9.3. You can continue to use the 4.3 versions of these with SAS 9.2.

Can SAS Enterprise Guide 4.3 and SAS Add-In for Microsoft Office 4.3 work with the SAS 9.3 BI web applications?
Yes. The integration features that allow these desktop applications to create, publish, and open SAS Web Report Studio reports will continue to work with the 4.3 web clients (SAS 9.2) and 4.31 web clients (SAS 9.3).

I hope that these answers are helpful; I expect that they might raise more questions that I haven't yet thought of. Add new questions to the comments, and I'll try to revise or amend this list in response.

6月 262011
 
About a year ago (wow, has it been that long?), I posted an example program that lets you report on the contents of a SAS information map. Using my example, you can see the data items, filters, and folder structure within a given information map.

Last week a reader posted a comment, wondering how to also learn the names of the SAS tables that contribute to the SAS information map's definition. I know that you can't get to that information using the information map dictionary tables, but I did learn that it is possible using PROC INFOMAPS and the LIST statement.

Here is an example that relates to the information map that I reported on earlier:


proc infomaps ;
 update infomap "Cars" mappath="/Shared Data/Maps";
 list datasources;
run;
 
The output goes to the SAS log:

Total datasources: 1
Data source: Sample Data.CARS_1993
ID: CARS_1993
Name: CARS_1993
Description:
 
Wow, I guess that was a pretty simple information map, with only one data source. I'm sure that you can come up with more complex examples.
6月 212011
 
We sometimes take it for granted, but the concept of the "SAS library" is just about one of the most awesome aspects of The SAS System.

You can give your library a name (a library reference, or libref), tell the system how to get to your data (options on a LIBNAME statement), and then build your SAS process to reference the contents of that library. Over time, you can change where your data is: put it in the file system, share it on a remote server, load it into DBMS. It doesn't matter; as long as the contents of the library have the same table names and column names/types, your SAS programs still work.

The SAS library definition (issued via a LIBNAME statement) tells your SAS programs how to get to the physical data. But these days, many SAS processes rely increasingly on metadata. Why is that? Among the reasons:

  • Accessing the physical data is expensive, in terms of I/O processing. Metadata can tell you about available data sources without expensive queries to the database.
  • Metadata is critical for building robust ETL processes and understanding impact of changes to downstream outputs (Example: "what reports are impacted if I change the name of this column?"). (You typically use SAS Data Integration Studio for this work.)
  • Metadata is easier to "secure", allowing you to assign privileges for users and groups to see only what you want them to see about the available data sources. (You use SAS Management Console for this work.)
  • Metadata can be used to "repackage" your data sources and represent them in friendlier business views (using SAS information maps, for example) without the expense of keeping multiple copies of the same data.
As important and flexible as it seems, the use of a metadata layer can present challenges to traditional SAS users who write programs or who use interfaces such as SAS Enterprise Guide to work with the data "closer to the metal". When you access data, are you going directly to the physical data or are you navigating through a metadata layer? There are behavior differences that result from the different approaches. It's important for SAS administrators to understand these behaviors so that they can provide the correct experience for the end users that they support.

Here are some resources to help. They answer the most common questions about metadata libraries, and provide guidance for how to get the behaviors that you want:

6月 172011
 
SAS-based processes are critical to many organizations, but sometimes the trickiest part of your job falls into one or both of these activities:
  • Getting stuff from the outside world "into" SAS. (Once it's in SAS, as many of you know, the world is your oyster.)
  • Getting the output of your SAS process "out" to the non-SAS people who need to consume it.
Here's a handy DATA step program that can copy file content from one place to another. It copies the content byte-by-byte, so it's great for copying files from your SAS session to a place outside of SAS, or vice versa.

/* these IN and OUT filerefs can point to anything */
filename in "c:\dataIn\input.xlsx";
filename out "c:\dataOut\output.xlsx";

/* copy the file byte-for-byte  */
data _null_;
  length filein 8 fileid 8;
  filein = fopen('in','I',1,'B');
  fileid = fopen('out','O',1,'B');
  rec = '20'x;
  do while(fread(filein)=0);
     rc = fget(filein,rec,1);
     rc = fput(fileid, rec);
     rc =fwrite(fileid);
  end;
  rc = fclose(filein);
  rc = fclose(fileid);
run;

filename in clear;
filename out clear;
 
It's true that you can copy disk-based files from one place to another by using operating system shell commands (via SYSTASK, for example). But the cool thing about the above program is that it can copy files to/from other places as well -- any location that you can access with a FILENAME statement, including URLs. For example, imagine that there is a file on the Web that you want to bring into SAS for analysis. Simply use FILENAME URL to define the IN fileref. Here's an example that grabs an Excel file from the Web and imports it into SAS:

filename in url "http://www.LotsOfData.org/data/data.xlsx"
  /* PROXY= is important for going outside firewall, if you have one */
  /* proxy="http://yourProxy.company.com" */
  ;
filename out "c:\temp\data.xlsx";

data _null_;
 length filein 8 fileid 8;
 filein = fopen('in','I',1,'B');
 fileid = fopen('out','O',1,'B');
 rec = '20'x;
 do while(fread(filein)=0);
  rc = fget(filein,rec,1);
  rc = fput(fileid, rec);
  rc =fwrite(fileid);
 end;
 rc = fclose(filein);
 rc = fclose(fileid);
run;

/* Works on 32-bit Windows */
/* If using 64-bit SAS, you must use DBMS=EXCELCS */
PROC IMPORT OUT= WORK.test
  DATAFILE = out /* the downloaded copy */
  DBMS=EXCEL REPLACE;
  SHEET="FirstSheet";
  SCANTEXT=YES;
  USEDATE=YES;
  SCANTIME=YES;
  GETNAMES=YES; /* not supported for EXCELCS */
  MIXED=NO; /* not supported for EXCELCS */
RUN;

filename in clear;
filename out clear;
 
Or, going the other way, perhaps you have a SAS stored process that creates a file that you want to position as a "download" file when the user runs the stored process in a web browser. In that case, you can use the reserved fileref _WEBOUT instead of the OUT fileref. For a specific example of this in action, see this tip about working with the Stored Process Web Application.
5月 262011
 
SAS Enterprise Guide has about 150 options that you can customize in the Tools->Options window. With each release, the development team adds a few more options that have been asked for by customers, and they rarely decommission any existing options. It's getting quite crowded on some of those options windows!

Alas, the team has not yet discovered how to implement the "Read my mind" option (but believe me, it's been requested), so it remains up to you to investigate the options that are offered and select those that suit the way that you want to work.

I use SAS Enterprise Guide every day to get work done, and I've got some favorite settings that I like to run with that help me to be more productive. Here are my top 5.

Replace results: Replace without prompting

How to find it: Tools->Options->Results->Results General, under the Managing Results heading.

The Replace Results option
After you set this option, you'll never again see this message "Do you want to replace results? Yes, No, or Cancel" when you re-run a task or program. Your results will always be replaced, which is what most people almost always want.

Turn off Show generated wrapper code in SAS log

How to find it: Tools->Options->Results->Results General...about halfway down the window. This option really helps to declutter your SAS log within SAS Enterprise Guide. I've written about this option in more detail in a previous post.

Show full details when viewing SAS libraries in File Dialog

How to find it: Tools->Options->General...about 1/3 of the way down the page. Checking this option allows you to see more details about the data sets in your libraries before you add them to your project. Read more about the File Details option in an earlier post.

Turn off Show Welcome window at start-up

How to find it: Tools->Options->General...top option. Or, check the box that says "Don't show this window again" the next time you see the "Welcome to SAS Enterprise Guide" window. This tells SAS Enterprise Guide, "Hey, the honeymoon is over. Get out of the way and let's get to work."

The Welcome window does provide handy one-click access to your most recently used projects. But you can also get to those via File->Recent Projects. Bonus added in the 4.3 release: File->Recent Programs, which provides easy access to SAS program files that you've opened recently.

Change default SAS Report style to Plateau

How to find it: Tools->Options->Results->SAS Report, Appearance section. The default ODS output style for SAS Report is Analysis. It's a nice clean style, but I prefer Plateau because it's a bit more concise and can fit more information on a screen. Here: take a look at this sample to see what I mean. There are lots of different styles: experiment and see which one you like the best.

Change Graph Format to PNG

How to find it: Tools->Options->Results->Graph, Graph Format. The PNG graph option

The default graph format for items such as bar chart and line charts is ActiveX. ActiveX has been the default output format in SAS Enterprise Guide since 1999, when the product shipped with version 1.0 on top of SAS Version 8. Back then, the quality of the charts produced using the ActiveX graph control was pretty impressive when compared to the output produced using GIF or similar static chart types.

The ActiveX device tells SAS to generate verbose scripting code as part of the SAS Report or HTML output. Then a client-side control is used to render the graph. This allows for some interactivity in the graph, but it also comes with a limitation: if the graph contains lots of data points, the script gets larger, and that can make for a very large output file.

SAS/Graph has come a long way since the early days of SAS Enterprise Guide, and with SAS 9.2 we like to recommend the PNG device for most applications. The PNG (or GIF or JPEG devices) respect the SAS graph styles and look pretty nice. If you use ODS Graphics (or the SGPLOT procedure), you get PNG already since ActiveX isn't supported.

Whoops, that's 6 options, not 5! Once I get going it's difficult to stop. I've got some other favorites that I recommend for certain situations (working with databases, making the best of network latency, collaborating with teammates on projects); those will provide good topics for a future post.

What about you? Do you have a favorite set of options for SAS Enterprise Guide, or even SAS in general? Post back in the comments.

5月 192011
 
I hope that the following statement is not too controversial...but here it goes: Microsoft Excel is not a database system.

That is, I know that people do use it as a database, but it's not an application that supports the rigor and discipline of managing data in the same way that "real" database systems do. Even Excel's more boring and less popular cousin, Microsoft Access, supports the discipline of database management much better than Excel.

Still, Excel spreadsheets are widely used because they are so flexible and can contain rich content, including charts and fancy formatting. These might be assets when it comes to creating compelling output, but it doesn't help a bit when your task is to analyze the data.

That's why the first step to analyzing spreadsheet data within SAS is to import the data into a SAS data set. The Import process allows you to impose the oft-needed discipline to the spreadsheet data "on the way in" to your SAS environment. For example, you can:

  • Select the data sheet, or an exact range within a sheet, that you want to import
  • Apply formatting rules to the data values so that they are treated appropriately (as character, numeric, date or time values, for example)
  • Control the field names so that you have SAS-friendly variable names in the resulting data set
Done properly, these steps will reduce the amount of "clean up" you need to perform before you can proceed with analysis.

The Import Data task within SAS Enterprise Guide (menu: File->Import Data) can lead you through this easily. For well-organized data in spreadsheets, the default settings are usually acceptable and you can simply review them and click Finish with no tweaking. For spreadsheet data that are less organized, the level of control that the wizard offers can help a lot.

We often get questions about how the import process works within SAS Enterprise Guide. For example, does it require SAS/ACCESS to PC Files? Does it generate a SAS program that you can reuse in batch code or in a stored process? Here are the answers:

SAS Enterprise Guide does not require SAS/ACCESS to PC Files in order to import Microsoft Excel content. SAS Enterprise Guide uses local Windows components to read the data from spreadsheets and then transforms them into a text-based format that SAS can import via DATA step. (Note: the same is true when importing Microsoft Access databases, Lotus worksheets, or even Paradox files.)

This means that when the step is complete you have your data in SAS, but you don't have a SAS program that represents the entire process. SAS Enterprise Guide does some of the work behind the scenes. That's convenient if you don't have SAS/ACCESS to PC Files on your SAS server. It's less convenient if you are trying to build a standalone batch program.

SAS Enterprise Guide can use SAS/ACCESS to PC Files if you want. If you do have SAS/ACCESS to PC Files on your Windows SAS server, you can check a box in the Advanced Options of the Import Data task and get SAS Enterprise Guide to generate a working program that uses PROC IMPORT with DBMS=EXCEL. This is similar to how the Import Data wizard works in the SAS display manager environment. However, there are a few requirements:

  • The Excel file must reside in the file system of the SAS server (your local PC if using local SAS, or the remote Windows system if your SAS workspace is on a different machine).
  • This is supported only when connecting to SAS on Windows. It is possible to use SAS/ACCESS to PC Files on a Unix system, but it requires a PC Files Server and the DBMS=EXCELCS option, which the Import Data task does not generate for you.
If all of those planets align for you, then you can use the Import Data task to create a reusable SAS program.

Exporting SAS data in Excel format does not require or use SAS/ACCESS to PC Files. Again, SAS Enterprise Guide uses the Windows-based APIs to create XLS files (or XLSX files with this custom task), and PROC EXPORT is not used for this. If you need the step to happen inside a SAS program, you can write your own PROC EXPORT step in a program node.

I hope that this clears up some of the questions and misconceptions we encounter. But if this post doesn't do it for you, let me know in the comments.

5月 102011
 
It's a simple task to use SAS to compute the number of weekdays between two dates. You can use the INTCK function with the WEEKDAY interval to come up with that number.

diff = intkc('WEEKDAY', start_date, end_date);
 
If you want to compute the number of working days between two dates, and assuming that every weekday is also a workday, this is perfect. However, most cultures observe certain non-productive days that they call "holidays", and this use of the INTCK function does not consider these when discounting the total sum of days.

Microsoft Excel supplies a function called NETWORKDAYS that can count the weekdays and consider a range of holiday dates when computing the total. This type of function is useful for project planners when they want to tick down the number of remaining days available to complete some work task. While there is no direct analogy to that function in SAS, it's not difficult to write your own functions by using the FCMP procedure. Here is an example of this function:


proc fcmp  outlib=work.myfuncs.dates;
  function networkdays(d1,d2,holidayDataset $,dateColumn $);

    /* make sure the start date < end date */
    start_date = min(d1,d2);
    end_date = max(d1,d2);

    /* read holiday data into array */
    /* array will resize as necessary */
    array holidays[1] / nosymbols;
    if (not missing(holidayDataset) and exist(holidayDataset)) then
       rc = read_array(holidayDataset, holidays, dateColumn);
    else put "NOTE: networkdays(): No Holiday data considered";

    /* INTCK computes transitions from one day to the next */
    /* To include the start date, if it is a weekday, then */
    /*  make the start date one day earlier.               */
    if (1 < weekday(start_date)< 7) then start_date = start_date-1;
       diff = intck('WEEKDAY', start_date, end_date);
    do i = 1 to dim(holidays);
    if (1 < weekday(holidays[i])< 7) and
         (start_date <= holidays[i] <= end_date) then
           diff = diff - 1;
    end;
    return(diff);
  endsub;
run; quit;
 
This function can read a range of holiday dates from a data set. Assuming that you have a data set named USHOLIDAYS with a date column named HOLIDAYDATE, you could use the function like this:

options cmplib=work.myfuncs;
/* test with data set of values */
data test;
  length dates 8;
  format dates date9.;
  infile datalines dsd;
  input dates : date9.;
  workdaysSince = networkdays(dates, today(), "work.usholidays","holidaydate");
datalines;
01NOV2010
21NOV2010
01DEC2010
01APR2011
;
 
This shows an example of the result:

Example output using this custom function

If you want to see the complete example with sample holiday data, I've placed it on the support site here. (Special thanks to my colleague Jason, whose paper I referred to, for his help in refining this example.)

5月 032011
 
SAS Enterprise Guide is best known as an interactive interface to SAS, but did you know that you can use it to run batch-style programs as well?

SAS Enterprise Guide has always offered an automation object model, which allows you to use scripting languages (such as VBScript or Windows PowerShell) to call the application's features from within other processes. (See Microsoft's "scripting center" for quick access to All Things Scripting for your Windows PC.)

It's this automation model that allows you to schedule a project or process flow to run unattended. When you select File->Schedule Project, SAS Enterprise Guide creates a default script file (using VBScript) and adds a scheduled task to the Windows scheduler. That's convenient, but you don't have to use the Schedule feature to take advantage of automation. You can write your own scripts to automatically run tasks, projects, or SAS programs at any time.

The automation model even allows you to create a new project on-the-fly, and add SAS programs into the project and run them. This way, you don't need to create a predefined SAS Enterprise Guide project (EGP file) in order to run SAS programs in your environment. The automation model uses the SAS Enterprise Guide application to provide the "plumbing" to access your SAS metadata environment and SAS workspace servers and run any SAS job...even when you don't have SAS on your local PC.

Note for Windows x64 users: SAS Enterprise Guide is a 32-bit application. In order to run scripts properly on a 64-bit operating system, be sure to use the 32-bit version of whatever scripting runtime is needed. For example, to run a VBScript file, use a command such as:


c:\Windows\SysWOW64\cscript.exe c:\projects\AutomationNewProgram.vbs
 
You can learn more about the automation API by perusing the reference documentation (the link is for the 4.2 version, but the 4.3 version is virtually unchanged). You can also learn from the examples provided within this SAS sample.

I've included an additional useful sample here. This VBScript program allows you to connect to your SAS workspace server and run any program code that you want, and then save the output log and listing to your local PC file system. To use the example, save it to a .VBS file on the machine where SAS Enterprise Guide is installed. Of course you'll need to change the names of the active profile and SAS server to suit your environment. Then you can run the example using the proper version of cscript.exe.


Option Explicit ' Forces us to declare all variables

Dim app         ' application
Dim project     ' Project object  
Dim sasProgram  ' Code object (SAS program)
Dim n           ' counter

' Use SASEGObjectModel.Application.4.2 for EG 4.2
Set app = CreateObject("SASEGObjectModel.Application.4.3")
' Set to your metadata profile name, or "Null Provider" for just Local server
app.SetActiveProfile("My Server")
' Create a new project
Set project = app.New

' add a new code object to the project
Set sasProgram = project.CodeCollection.Add

' set the results types, overriding app defaults
sasProgram.UseApplicationOptions = False
sasProgram.GenListing = True
sasProgram.GenSasReport = False

' Set the server (by Name) and text for the code
sasProgram.Server = "SASApp"
sasProgram.Text = "proc means data=sashelp.cars; run;"

' Run the code
sasProgram.Run          
' Save the log file to LOCAL disk
sasProgram.Log.SaveAs "c:\temp\outputAuto.log"  

' Filter through the results and save just the LISTING type
For n=0 to (sasProgram.Results.Count -1)
  ' Listing type is 7
 If sasProgram.Results.Item(n).Type = 7 Then
    ' Save the listing file to LOCAL disk
   sasProgram.Results.Item(n).SaveAs "c:\temp\outputAuto.lst"  
  End If        
Next
app.Quit