Keep Your Hands Off of My SAS Data Sets!

SAS Programming Professionals,

I am all about sharing.  Knock on my door and I will gladly lend you a stick of margarine, a cup of sugar, an egg or two, some flour, a corkscrew, or a beer.  Not a problem to borrow a tie, one of my extra belts, a white shirt (if it fits), a scarf, or a pair of gloves.  Sure, I will gladly lend you one the books in my home library, a couple of my music Cd's, or one of my movie DVDs.  I am reasonably sure that most of these items will either be returned to me in due time, or reciprocated, or paid forward.  But, keep your hands off of my SAS data sets!

I would bet that final sentiment regarding SAS data sets is pretty prevalent in organizations where SAS programmers work with shared storage resources.  Whether you are storing your SAS data sets in server directories or on network directories, you do not want to have them deleted, resorted, updated, or otherwise overwritten by other programmers on your team.  At least, not without your permission.

Fortunately, most organizations create security permissions that largely safeguard against unauthorized access of data.  They implement security packages--either native to the OS or purchased from vendors--to ensure that data is accessed only by those with the need to know.  Groups of programmers are given access to specific directories and other staff, with the exception of systems administrators, cannot get into them or see them at all.  Despite all of this protection, data integrity issues can come from programmers within your own group; programmers who have access to the same directories that you do; programmers who have the same access rights that you do.

Unfortunately, there is no foolproof way for you to keep your teammates from crunching your SAS data sets.  But, here are a few protection measures that you can put into place to help safeguard your SAS data sets:

• SAS Data Set Passwords.  SAS allows you to specify ALTER, READ, and WRITE passwords.  Users must know the password in order to process password-protected data sets with SAS.  So, you can specify passwords for your do-not-disturb SAS data sets, wait for your co-workers to complain, and determine if they really do need access to those data sets.

• ACCESS=READONLY Libname Option.  This option specifies that no data sets in the library can be updated and no new files can be written to the library.  This option can be effectively deployed in a shared group AUTOEXEC.sas file.  If your colleagues grumble, tell them to copy the data sets in question to one of their libraries and process them there.  Caution them to be mindful of version control issues.

• SAS Views.  Create views of your SAS data sets via the DATA step or PROC SQL and allow your colleagues to use the views instead of the actual data sets.  The beauty of this approach is that you can have your valued data sets in one directory and the views in another.  Point your coworkers at the views directory and do not tell them the whereabouts of the permanent data sets.

• Generation Groups.  You can specify for SAS to keep several generations of your SAS data sets available.  Consequently, when one of them is modified, you will still have the older version available.

• SAS Audit Logs.  SAS Audit Logs can be used to determine who updated SAS data sets after the fact. They don't stop your colleagues from actually updating SAS data sets, but you can use them to determine the who, what, and when so that you can storm into the right office for an explanation.

• LOCK Statement.  You could use the LOCK statement to lock your SAS data sets so that no other SAS program can read or write to the file.  This is a bit extreme and would require that your SAS program with the LOCK statements be running for the duration of when you wanted to safeguard your SAS data sets.  This option can be effectively deployed in a shared group AUTOEXEC.sas file.

• Zip Files.  You could simply zip your SAS data sets up into zip files, delete the permanent SAS data sets, and restore the data sets from the zip files when you need them.  This is another extreme measure, but if your data sets get clobbered on a regular basis, you may find it more appealing than having them constantly restored.

Obviously, the best solution for keeping your important SAS data sets from being updated by your teammates is good communications between all involved, and a shared set of best practices for accessing and modifying SAS data sets.  But, when that is not available, you may have to reach for some of the ideas I have posted here.

Oh, and about that book that you borrowed from my SAS bookshelf library a couple of months ago... can I get it back?

Best of luck in all your SAS endeavors!

----MMMMIIIIKKKKEEEE

aka Michael A. Raithel

Amazon Author's Page:  

http://www.amazon.com/Michael-A.-Raithel/e/B001K8GG90/ref=ntt_dp_epwbk_0 

We Don't Need No Education!

SAS Programming Professionals,

So, how did you first learn SAS programming?

Originally, I was self taught.  Many years ago, I learned SAS on the job when a systems programmer quit and I took over supporting a mainframe performance software package that was written in SAS. I got a copy of the Base SAS users guide and the SAS Procedures guide and learned how to write my own SAS programs.  It was new and fun; and SAS was more powerful than any of the other programming languages I had been using.

After I had been programming with SAS for about three years, my then employer finally paid to send me to a SAS class.  I entered that class pretty confident of my SAS programming abilities, but was humbled within the first hour.  I had been running my SAS programs in batch on mainframe computers.  All of the students in the class ran their SAS programs interactively with SAS Display Manager, which I had never seen before.  So, I had to catch up to their level of expertise just to do the class exercises.

It was a great class!  I learned to use the SAS Display Manager; the proper ways to perform match merges; how the Program Data Vector works in the DATA step; the intricacies of PROC MEANS and its first cousin PROC SUMMARY; and a host of other very useful programming techniques. I learned the fundamentals of SAS that I had missed by simply jumping into using it and getting all of my information from the manuals.  That class made me a much stronger SAS programmer.

Today's programming professionals who are interested in taking formal SAS classes to increase their SAS programming acumen have a lot of choices.  SAS currently has the following training formats:

• Classroom.  You can take an instructor-led SAS class in one of SAS's state-of-the-art training facilities located throughout the US and in many other countries.

• Live Web Classroom.  Let an instructor-led SAS class come to you via the Internet.  This format saves on travel costs and time out of the office.

• E-Learning.  This option allows you to take SAS classes at your own pace via the Internet 24/7.  This is another way to save travel costs and time out of the office.

• On-Site Training.  Have qualified SAS instructors come on-site to teach you and your colleagues various SAS courses.

• Mentoring Services.  This option provides you with a SAS instructor who becomes your coach to help you learn how to write SAS programs that address your organization's unique data processing needs.

You can find out much more about available SAS classes being offered, their content, when they are scheduled, and the training formats in the Training section of the support.sas.com web site:

    http://support.sas.com/training/index.html

Check it out because there is likely a SAS class that would make you a stronger programmer in a format that fits with your own work schedule.

"We don't need no education"?  Nope, that couldn't be further from the truth!

Best of luck in all your SAS endeavors!

----MMMMIIIIKKKKEEEE

aka Michael A Raithel

Check out my SAS books:  http://www.amazon.com/Michael-A.-Raithel/e/B001K8GG90/ref=ntt_dp_epwbk_0

Don't Call Me an April Fool!

SAS Programming Professionals,

With over 50,000 certification credentials awarded to people around the world, the SAS Global Certification program is obviously meeting the needs of a goodly number of SAS programming professionals.  Interested SAS software users can demonstrate an in-depth understanding of SAS by earning credentials in SAS Programming, SAS Analytics, SAS Administration, SAS Data Management, and SAS Business Intelligence.  They study the material, master it, take the test, and earn the credential.

You can learn more about the SAS Global Certification program by visiting this link: //support.sas.com/certify/

As it stands now, the two SAS Base Programmer certification tests require a person to answer multiple choice and short-answer questions.  Personally, I am locked-and-loaded for the day that one of those exams requires an essay.  I know that the secret to getting a good grade on an essay question is not to simply answer the prompt.  The secret is to add additional information about the topic which shows that you have a mastery of the overall subject matter.  Since I have been programming with SAS for a very long time, I have a lot of very sage and insightful comments that I would put into my essay.  So, I have no doubt that I would crush it!

Here are some of the clever things I have learned along the way that I would weave into my essay:

• The “DO WHILE” statement is known as the “Stalactite DO” since it is evaluated at the top.  The “DO UNTIL” statement is known as the “Stalagmite DO” since it is evaluated at the bottom.

• When I need to fix a SAS program with a hard-to-find error, I just put a %MEND at the bottom and rerun it.

• An example of a SAS regular expression is:  “Don’t forget the semicolon”!

• The SAS Log is basically a waste of time and effort.  I never use it because it only prints bad news!

• I sometimes code: PROC OPTIONS; ...without the “RUN;” statement, just to keep my OPTIONS open!

• A known SAS programming trick is: If you code a DROP statement and a KEEP statement for the same variable in a DATA step, you will see a bright flash on your monitor as they cancel each other out.

• It is difficult to determine if PROC MIXED has executed successfully because it prints MIXED messages.

• The SAS Display Manager is a long-time SAS Institute employee who is responsible for supervising the setup of SAS Institute booths at conferences and trade shows.

• Despite its strong odor, the RANK procedure is very popular among SAS programmers serving in the US armed forces.

• Due to heightened security concerns, SAS programs containing PROC EXPLODE have been banned from all government buildings, airports and rail stations.

• A recent survey of American SAS programmers found that most use PROC IMPORT far more than they use PROC EXPORT.  However, the same survey determined that this did not significantly add to the United States trade deficit.

 

Okay, so do I really have to say:  April fools!  Nah, didn't think so.

Best of luck in all your SAS endeavors!

----MMMMIIIIKKKKEEEE
aka Michael A. Raithel
Check out my SAS books:  http://www.amazon.com/Michael-A.-Raithel/e/B001K8GG90/ref=ntt_dp_epwbk_0

Raiders of the Lost Spreadsheet

SAS Programming Professionals,

Have you ever peered intently into an unfamiliar data delivery directory, realized what was in it, rolled over onto your side, stared blankly into the distance, and dejectedly uttered something akin to:

    "Spreadsheets!  Why did it have to be spreadsheets?"

If so, then we are definitely on the same page.  Why does it always have to be spreadsheets?

The answer to that question is actually pretty obvious when you think about it.  The popularity of Microsoft Office has made Excel one of the most popular mediums for storing data.  It is used extensively in grade schools, middle schools high schools, and colleges.  People with home businesses use it; office administrators use it; clerical staff use it; scientists use it; lawyers use it; hospital workers use it; Federal, state and local government workers use it; and programmers use it too.

An individual who needs to store data in electronic format and then process it may not have SAS, or C++, or JAVA, or C#, or PYTHON, or PHP, or R, or MATLAB, or ColdFusion, or FOCUS, or FORTRAN, or Groovy, or JavaScript, or MOBY, or MUMPS, or NATURAL, or Perl, or PHP, or PL/SQL, or PowerShell, or Python, or S-PLUS, or Visual Basic installed on his or her PC.  But, that person will undoubtedly have Microsoft Office and thus have Excel.  That is why it always has to be spreadsheets.

But, processing data stored in spreadsheets is not really a problem for intrepid SAS programmers.  When I go on a data exploration expedition where there is a good chance of encountering spreadsheets, I pack the usual:  my brown leather jacket, fedora, and bullwhip.  But, most importantly, I put SAS/Access Interface to PC Files into my backpack.

SAS/Access Interface to PC Files is a SAS for Windows product that allows you to read, write, and update data in Excel and Access.  As such, it is a must-have for your Windows SAS installation.

Here is an example of a program that I use to map out the contents of an unexplored spreadsheet:

   ods rtf file="G:\BigProject\Worksheets in NewDataSpreadsheet.rtf";

   libname xlslib "G:\NewProject\DeliveryDirectory\NewDataSpreadheet.xlsx" access=readonly;

   proc sql;
   create table WorkSheets as
   select distinct(compress(MEMNAME,"',$")) as WorkSheet_Name,
       name as ColumnName
   from dictionary.columns
   where libname = 'XLSLIB';
   quit;

   proc print noobs data=WorkSheets;
   var WorkSheet_Name ColumnName;
   title1 "Workseets in NewDataSpreadheet.xlsx";
   run;

   ods rtf close;

The ODS statement specifies that my report will be created as an RTF document.  Because I have SAS/Access Interface to PC Files, the LIBNAME statement allocates the NewDataSpreadheet.xlsx spreadsheet much the same way as it would for a SAS data set.  (Notice that I specified access=readonly so that I do not accidentally update the spreadsheet).  Since I have "LIBNAME-d" the spreadsheet, information about its worksheets and column names is now available in the SAS Dictionary Tables.

I use PROC SQL to extract the name of each worksheet (variable WorkSheet_Name) in the Excel file; and the names of the columns (variable ColumnName) within each worksheet and then plop them into a SAS data set for further exploration. The code snippet compress(MEMNAME,"',$") gets rid of the annoying quotes and dollar signs that are found in spreadsheet MEMNAMEs.  Then, I use the PRINT procedure to create a report.  A simple, neat, quick, and easily macro-tized piece of code.

Here are several good references that you can use to find out more about processing spreadsheets with SAS:

• SAS Access product information on the SAS website:  http://support.sas.com/software/products/access/index.html
• Exchanging Data between SAS and Microsoft Excel: Tips and Techniques to Transfer and Manage Data More Efficiently, by Bill Benjamin  http://support.sas.com/publishing/authors/benjamin.html
• Conference papers by Vince DelGobbo such as: Creating Multi-Sheet Microsoft Excel Workbooks with SAS®: The Basics and Beyond Part 1 http://support.sas.com/resources/papers/proceedings14/SAS050-2014.pdf 

Armed with those resources, some pluck, a sense of adventure, and with your own trusty copy of SAS/Access Interface to PC Files, you too can be a raider of the lost spreadsheet!

Best of luck in all your SAS endeavors!

----MMMMIIIIKKKKEEEE
aka Michael A. Raithel
Amazon Author's Page:
http://www.amazon.com/Michael-A.-Raithel/e/B001K8GG90/ref=ntt_dp_epwbk_0 

What is a SAS Index Good for Anyhow?

 SAS Programming Professionals,

If there was a SAS performance tool that could drastically reduce your program’s I/O’s, lower its CPU time, and decrease its run time, would you use it?  Of course you would!  Such a performance tool exists; it is called a SAS index.  SAS Indexes can dramatically improve the performance of programs that access small subsets of observations from large SAS data sets.  They do this by only accessing and returning the observations that you specify in a WHERE expression, instead of reading the entire SAS data set.

It is easy to understand how a SAS index can help you to directly access the observations that you need in a particular SAS data set.  As an exercise, do the following:

• Open support.sas.com in a web browser
• Type "rtrace" in the search window at the top of the page
• Click on the Search button

The support.sas.com search function returns about seventy links.  When you click on any of those links, you get a page in the documentation that discusses the SAS RTRACE facility.  This saves you the tedious effort of going through the entire SAS Online documentation, page-by-page, looking for occurrences of the word “rtrace”.

A SAS index is analogous to the search function discussed above.  A good index allows your programs to quickly access the subset of SAS observations that you need from a large SAS data set when you specify a key variable value (or values) that must be matched.  This can dramatically improve the speed and efficiency of your SAS programs.

Conversely, badly conceived SAS indexes return far too many observations and are no better than reading the entire data set sequentially.  In the analogy, above, consider how many pages would be returned and how much longer it would take if you searched the SAS Online Documentation for the word “SAS”.  That is why it is important to know more about the selection criteria for index variables, as well as the actual creation and use of SAS indexes.

After deciding that an index is appropriate for your subsetting purposes, you have three tools to choose from to create one: 

1. The DATASETS procedure, 
2. The SQL procedure, 
3. The DATA option in the DATA step or in a Procedure.  

When you first use one of these tools to create an index, SAS creates a separate index file and associates it with your SAS data set.  The index file has the same name as the original SAS data set, but has a suffix of ".sas7bndx."  SAS stores additional indexes in that file and deletes the file when all indexes have been removed from the data set.

You can create a Simple index from a single variable, or a Composite index from two or more variables.  A SAS data set can have as many indexes as you think are necessary.

You can exploit indexes with the WHERE statement, the BY statement, or with the KEY statement used in conjunction with either a SET or MODIFY statement.  In doing so, you will be increasing the efficiency of your SAS programs that use the index.  That is what SAS indexes are good for!

There is enough information about SAS indexes to fill an entire book.  Here are a few resources to consider if you are interested in learning more about SAS indexes:

• The Complete Guide to SAS Indexes, book by Michael A. Raithel  http://www.amazon.com/Michael-A.-Raithel/e/B001K8GG90/ref=ntt_dp_epwbk_0
• The Basics of SAS Indexes, tech paper by Michael A. Raithel  http://www2.sas.com/proceedings/sugi30/247-30.pdf

• Understanding SAS Indexes, SAS Institute documentation  http://support.sas.com/documentation/cdl/en/lrcon/67885/HTML/default/viewer.htm#n06cy7dznbx6gen1q9mat8de6rdq.htm

If you find that your programs are consistently extracting very small subsets of observations from very large SAS data sets, then SAS indexes just might be the right tool for you.

Best of Luck in all your SAS endeavors!  

----MMMMIIIIKKKKEEEE 

aka Michael A. Raithel 

Amazon Author's Page: 

http://www.amazon.com/Michael-A.-Raithel/e/B001K8GG90/ref=ntt_dp_epwbk_0 

I Know What You Did Last Summer!

SAS Programming Professional,

I know what you did last summer.

If it was unintentional, then you probably don't know what I am talking about.  If it was intentional, then you probably thought that I would never find out.  Either way, the damage is done.  The actions that you took on that warm summer evening are as clear to me now as they would have been if I had been watching over your shoulder while you did them.  I know what you did last summer: You updated one of my SAS data sets.

We work on the same project  and both have read, write, update, and delete rights to the project's directories.  The production SAS data set that I created for the spring data delivery was inexplicably updated in the summer.  And, you were the one who did it.  Because we have been teammates for a while, I am giving you the benefit of the doubt.  I bet that you made a copy of the production SAS program for a different use, updated it, but forgot to change the LIBREF to point to your test SAS data library.  So when you ran it, you accidentally deleted 400 observations and updated 273 observations in the production data set.

Oh, you want to know how I determined it was you and how I know exactly what changed.

Well, because that production data set is very important, I used PROC DATASETS to create a SAS audit trail file for it.  SAS audit trails record changes to SAS data sets.  They can record the before and after image of observations that were changed, the type of change, the date/time of the change, and the userid of the person who changed the SAS data set.  So, SAS audit trails can be very useful in a shared directory environment where many staff members have access to important SAS data sets.

Here is the code I used to create the audit trail for the production SAS data set:

proc datasets library=prodlib nolist;
        audit SpringDeliveryData;
        initiate;
        log admin_image=yes
              before_image=yes
              data_image=no
              error_image=yes;
        run;
quit;

When I executed that DATASETS procedure code, SAS created a file named SpringDeliveryData.sas7baud in the same directory as the SAS data set.  When an observation is updated, added, or deleted from SpringDeliveryData, SAS writes an observation to the audit trail data set containing the variables in the original SAS data set and six specific audit trail variables.  Of note are _ATDATETIME_ which specifies the date/time of the change; _ATOPCODE_ which specifies the type of change that took place--e.g. add, delete, modify; and _ATUSERID_ which specifies the userid of the person whose SAS program made the change.

When I noticed that SpringDeliveryData had been modified, I used a PROC PRINT to dump the audit trail file.  That is how I know that the data set was updated at 5:27 PM on August 5th by a program submitted under your userid.

You are interested in using SAS audit trails for your own production SAS data sets?  Great!  You can find a comprehensive write-up in the documentation on support.sas.com at this link:

http://support.sas.com/documentation/cdl/en/lrcon/67885/HTML/default/viewer.htm#n0ndg2uekz7qkbn1caoki2hzqx8l.htm

Don't fret about the updates to the SpringDeliveryData SAS data set.  I am going to request that our systems administrator restore the data set to the day before the summer update.  That way, we will have the original data set available in case our client has questions about it.

Good to know that I was right that you accidentally updated the production data set last summer.  Oh, don't go.  Unfortunately we have one more thing to talk about:

I know what you did last fall...

Best of luck in all your SAS endeavors!

----MMMMIIIIKKKKEEEE
aka Michael A Raithel
Amazon Author's Page:  http://www.amazon.com/Michael-A.-Raithel/e/B001K8GG90/ref=ntt_dp_epwbk_0

Excuse Me; But, Is That a 32-Bit or a 64-Bit SAS Catalog?

SAS Programming Professionals,

I don’t know about you, but I get pretty determined to prove them wrong when people tell me that I cannot do something.  I am not talking about fantastical things such as flying through the heart of the sun and out the other side without getting burned.  Nor, am I talking about social things like becoming president of the United States or an author on the New York Times Bestseller list.  And, I am not talking about physical things such as swimming 2.4 miles, biking 112 miles, and running 26.2 miles back-to-back on the same day.  No, I am talking about being told that I cannot do something with SAS.

For example, I was once told:

• that you could not summarize impossibly large SAS data sets to load a data warehouse.  So, I figured out a way to do it:  http://www2.sas.com/proceedings/sugi22/DATAWARE/PAPER113.PDF 

• that you could not measure the performance of SAS/IntrNet application programs.  So, I figured out a way to do it: http://www2.sas.com/proceedings/sugi31/226-31.pdf 

• that you could not determine which SAS products individual staff members were using on shared servers.  So, I figured out a way to do it: http://www2.sas.com/proceedings/sugi29/215-29.pdf 

• that you could not create a chargeback system for UNIX and Linux systems without purchasing an accounting package.  So, I figured out a way to do it:  http://www2.sas.com/proceedings/forum2007/194-2007.pdf

      Consequently, when I was told that there was no SAS facility for programmatically determining whether a Windows SAS catalog was a 32-bit catalog or a 64-bit catalog, I resolved to figure out a way to do it.

The background is that my organization plans to migrate from 32-bit SAS to 64-bit SAS as part of a SAS 9.3 to SAS 9.4 upgrade.  SAS data sets are compatible between the two bitages, but SAS catalogs are not.  Stating the problem: you cannot open a 64-bit SAS catalog with 32-bit SAS.  So, it is advantageous to have a tool for determining which SAS catalog is which bitage as you move forward into a mixed-bit programming environment during the transition.

I did my due diligence and researched every place that I thought I might be able to find a way to differentiate the bitage.  An indicator in PROC CATALOG if I ran it with the STAT option enabled?  Nope.  Something in the directory portion of a PROC CONTENTS listing with the DETAILS option specified?  Nope.  A lesser-known option of PROC DATASETS?  Nope.  How about a flag in the Dictionary Tables CATALOGS table or in the SASHELP Views VCATALG view?  Nope.  A Usage Note on support.sas.com.  Nope.  A  SAS technical paper published at either SAS Global Forum or a Regional SAS Users Group?  Nope, not that either.

I figured that if you could not tell the difference within SAS, itself, how about if you looked at the catalogs as simply files.  So, I got a 32-bit SAS catalog and a 64-bit SAS catalog and opened them with WordPad to take a look inside.  Bingo!  There was enough information in the very first record of both catalog files to determine the difference.  So, I wrote a program that tested for the string of characters that told the tale.

Here is the SAS program that I wrote:

/*Macro to determine bitage of a SAS catalog */

%MACRO Test_Cat_Bitage(SASCatalog);

filename sascat "&SASCatalog";

data decompcat(keep=CAT_BITS SAS_Catalog);

length CAT_BITS $8

         SAS_Catalog $50;

infile sascat obs=1 truncover;

input bigline $charzb32767. ;

if index(bigline, "W32_7PRO") > 0 then CAT_BITS = "W32_7PRO";

      else if index(bigline, "X64_7PRO") > 0 then CAT_BITS = "X64_7PRO";

      else CAT_BITS = "Unknown ";

SAS_Catalog = strip("&SASCatalog");

label CAT_BITS    = "Bitage of SAS Catalog"

        SAS_Catalog = "Full Path SAS Catalog Name"

        ;

run;

proc append base=AllCatalogs

                  data=decompcat;

run;

%MEND Test_Cat_Bitage;

/* Example of executing the macro to read a catalog file */

%Test_Cat_Bitage(c:\temp\gender.sas7bcat);

As you can see, the program determines the bitage of a SAS catalog by treating the catalog as a file, not as a catalog.  It opens the catalog file and inspects the first line for a specific character string: W32_7PRO for 32-bit catalogs; X64_7PRO for 64-bit catalogs.  Once it determines the bitage, the program writes an observation to data set AllCatalogs in the WORK library.  Each observation in AllCatalogs has two variables:  CAT_BITS, which specifies whether the catalog is 32 or 64 bits, and SAS_Catalog, which is the full path name of the SAS catalog file.  

The object of this particular setup is to run the macro against several, a score, dozens, hundreds, or thousands of SAS catalogs and build a SAS data set which identifies their bitage.  After that, one may choose to copy AllCatalogs to a permanent SAS data set, or create a report from it.  Or both.

Being a talented SAS programmer yourself, I would bet that you also do not like it when people tell you that you cannot do something with SAS.  Right?  Yea, it goes with the territory.  How about posting a comment telling us about a particularly difficult SAS problem you encountered and the clever way that you resolved it?  Bet you can’t do that.

Best of luck in all your SAS endeavors!

----MMMMIIIIKKKKEEEE
(aka Michael A Raithel)
Amazon Author's Page:  http://www.amazon.com/Michael-A.-Raithel/e/B001K8GG90/ref=ntt_dp_epwbk_0