Monday, September 26, 2016

Hack 3.20 Saving Duplicate Observations Removed by PROC SORT

SAS Programming Professionals,

Did you know that you can have SAS store duplicate observations that are removed during a sort that uses the NODUPKEY option in a separate SAS data set?

The DUPOUT= option of PROC SORT specifies a SAS data set that is used to store observations eliminated from a sorted data set when the NODUPKEY option is employed.  SAS sorts the specified data set and each observation that has duplicate BY statement variable values is written to the DUPOUT= data set as it is eliminated from the sorted data set.  Here is an example:

proc sort nodupkey
      data=sashelp.class
      out=sorted_class
      dupout=dupkeys
      ;
      by sex age;
run;

The SAS log for this example looks like this:

1    proc sort nodupkey data=sashelp.class out=sorted_class dupout=dupkeys;
2        by sex age;
3    run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.               
NOTE: SAS sort was used.
NOTE: 8 observations with duplicate key values were deleted.
NOTE: The data set WORK.SORTED_CLASS has 11 observations and 5 variables.
NOTE: The data set WORK.DUPKEYS has 8 observations and 5 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Note that 8 observations with duplicate values of SEX and AGE were written to the DUPKEYS SAS data set.

This can come in very handy for QC-ing programs where you are eliminating duplicate-keyed observations during a sort.  You can double-check the observations in the DUPOUT= SAS data set and verify that the sort really got rid of observations that are not needed in the original sorted data set.

Best of luck in all your SAS endeavors!

----MMMMIIIIKKKKEEEE
(aka Michael A. Raithel)

Excerpt from the book:  Did You Know That?  Essential Hacks for Clever SAS Programmers

I plan to post each and every one of the hacks in the book to social media on a weekly basis.  Please pass them along to colleagues who you know would benefit.

Monday, September 19, 2016

Hack 3.18 Moving Catalog Entries Between SAS Catalogs

SAS Programming Professionals,

Did you know that you can easily copy or move catalog entries between SAS catalogs?

The COPY statement of PROC CATALOG allows you to copy or to move catalog entries between SAS catalogs.  This can come in handy when you are working on creating lots of formats, and they end up in your Work library.  Or, when you receive a format catalog from a client and want to copy the entries to a catalog that is normally allocated in your SAS programs.  Here is an example:

libname cperm "c:\permanent_lib";

proc format;
      value $gender
      "M" = "Male"
      "F" = "Female"
      ;
      value agecat
       low - 12  = "Pre Teen"
       12 - 14   = "Young Teen"
       15 - high = "Older Teen"
       ;
run;

data cperm.class;
set  sashelp.class;

format sex $gender.
       age agecat.;
run;

/* Move catalog entries to permanent catalog*/
proc catalog catalog=work.formats;
      copy out=cperm.Myfmts move;
run;
quit;

/*Verify the catalog entries were moved*/
proc catalog catalog=cperm.Myfmts;
      contents;
run;
quit;

In the example, the LIBNAME statement, FORMAT procedure, and DATA step simply set up our example situation.  We create two format entries ($GENDER and AGECAT) in the default Work library catalog.  Then, we create a permanent SAS data set and assigned those formats to it.  We will be in “trouble” the next time we access the permanent data set, because the $GENDER and AGECAT formats will have evaporated when our SAS program, above, ends.

So, we use PROC CATALOG to move the formats to a permanent format catalog (CPERM.MYFMTS) that is always allocated in our autoexec.sas file when we run our SAS programs.  Note that the MOVE option specifies to move the catalog entries, not to simply copy them.  After that operation, WORK.FORMATS is going to be missing two format entries!

Here is the log from the final PROC CATALOG:

Contents of Catalog CPERM.MYFMTS

#    Name        Type               Create Date          Modified Date    Description
---------------------------------------------------------------------------------
1    AGECAT      FORMAT     13Feb12:13:27:32       13Feb12:13:27:32
2    AGEGROUP    FORMAT     17Sep07:10:55:21       17Sep07:10:55:21
3    GENDER      FORMATC    13Feb12:13:27:32       13Feb12:13:27:32

…showing that all of our hard work paid off!

Best of luck in all your SAS endeavors!

----MMMMIIIIKKKKEEEE
(aka Michael A. Raithel)

Excerpt from the book:  Did You Know That?  Essential Hacks for Clever SAS Programmers

I plan to post each and every one of the hacks in the book to social media on a weekly basis.  Please pass them along to colleagues who you know would benefit.

Monday, September 12, 2016

SAS & Bugs & Rock & Roll


SAS Programming Professionals,
 
SAS & bugs & rock & roll? But, of course!
 
SAS
Because of its amazing versatility, SAS is indisputably the greatest software package currently in use anywhere within the Milky Way Galaxy.  Can SAS input every type of flat file imaginable?  Yes!  Can SAS read and write to relational database management systems?  Yea!  Can SAS perform predictive analysis?  Yup!  Can SAS facilitate end-user analysis of data via web-based GUI’s?  Uh-huh!  Can SAS be used to Extract?  Of course!  Can SAS be used to Transform?  Affirmative!  Can SAS be used to Load?  Well, duh!  Can SAS help you to lose weight?  Aw c’mon; let’s not be silly here! 

Given that SAS is so powerful and that you want to either learn it or to learn more about it, here are a few helpful resources:
  • Learning SAS – You can either learn SAS from scratch or learn something new about SAS from SAS Education.  They offer dozens of classes in various formats that range from classroom lectures/exercises at SAS training facilities to e-learning classes that you can take from the privacy of your own office.  There is likely a SAS class just waiting for your own particular needs at:  http://support.sas.com/training/
  • SAS Documentation – Documentation on every SAS product can be found on the support.sas.com web site at the following URL:  http://support.sas.com/documentation/  The documentation is entirely free and easily searchable via a number of facilities on the documentation web pages.
  • Using SAS – There is no substitute for experience; and you can learn how to use SAS from a legion of SAS programming professionals by reading the technical papers they have published as SAS conferences.  Two great links for this are:
 Now that you have some ideas about SAS information, you should:  Extract it from the classes, documentation and technical papers; Transform the way that you write your SAS programs; and Load those new ideas and techniques into your professional SAS programming assignments.
 
& Bugs
So, when was the last time that you wrote a SAS program from scratch and didn’t have a single NOTE:, WARNING:, or ERROR: message in the log?  Right; thought so; same here!  Bugs are as much a constant of SAS programming as they are of any other type of programming.  There are generally two types of bugs in SAS programs:  logic bugs and programming bugs.  Logic bugs are generally harder to catch because they are tied to the algorithms you are using to Extract, Transform, and Load your data in your SAS programs.  They may result from bad specs, an insufficient understanding of the programming assignment, unruly data, or other meta-programming circumstances.  Programming bugs result from the way that you code SAS programing language statements to Extract, Transform, and Load data.
 
Fortunately, the SAS log is a great source for identifying and correcting programming bugs in SAS programs.  But, don’t take my word for it; check out a few recent papers from the experts:
Of course, if your attitude is summed up by the infamous quote:
 
The SAS Log is basically a waste of time and effort.  I never look at it because it only prints bad news!
 
…then the aforementioned links are not going to be of any help.  None at all!
 
& Rock & Roll
Do you listen to music when you are writing SAS programs?  I do.  But, the type of music that I listen to is entirely dependent upon the type of SAS program that I am writing.  If I am writing a new program with complicated logic, then I like to have either Spa or Ambient music playing in the background.  If it is a run-of-the-mill SAS program, then it’s got to be rock!
 
I have eclectic tastes in rock music, but my go-to-bands for SAS programming are (in no particular order):  Led Zeppelin, Pink Floyd, Nirvana, the Who,  Nine Inch Nails, David Bowie, Depeche Mode, the Cure, and Green Day.
 
You can see that “classic rock” is well represented in my list of favorite groups to program by.  If you are a fan of classic rock and want to find a classic rock station in your own particular corner of the US of A, then check out SAS data visualization guru Robert Allison’s classic blog post:
 
When did ‘your music’ become ‘classic rock’
 
Robert’s informative post will also show you the top 20 rock artists overall in the 25 radio stations in the sample, so that you will know what to expect.  What to expect—that is—besides great music with which to write your serious SAS programs.
 
SAS & bugs & rock & roll?  Yep; just another great day in the office!
 
Best of luck in all your SAS endeavors!
 
----MMMMIIIIKKKKEEEE
(aka Michael A. Raithel)
http://www.amazon.com/Michael-A.-Raithel/e/B001K8GG90/ref=ntt_dp_epwbk_0

Tuesday, September 6, 2016

Hack 3.19 Moving SAS Data Sets with PROC COPY

SAS Programming Professionals,

Did you know that you can physically move a SAS data sets from one directory to another using PROC COPY?

Using PROC COPY obviates your having to move SAS data sets manually via Windows Explorer or SAS Explorer.  For example, let’s move a couple of SAS data sets from a temporary directory to a permanent directory:

libname templib "c:\temp";

libname mylib "H:\My Documents\My SAS Files\9.4";

proc copy in=templib out=mylib move;
      select air buy;
run;

In this example, we moved both the AIR and the BUY SAS data sets from the c:\temp directory to the H:\My Documents\My SAS Files\9.4 directory.  Thereafter, if we were to view the directories in Windows Explorer or SAS Explorer, we would see that they have indeed been moved.

There are a few other options that you may need to specify to successfully move SAS data sets that have additional features:

·        ALTER – Is used when you are using the MOVE option to move files that are have an ALTER password.

·        CLONE – Specifies whether to copy the following file characteristics to the file that is moved:
o   Input/output buffer size
o   Data set compression
o   Whether free space is reused in compressed data sets
o   Whether compressed data sets can be accessed by observation number
o   Data representation
o   Encoding value

·        CONSTRAINT = YES | NO – Copy existing integrity constraints when moving a data set.

·        FORCE – Must be used to MOVE a SAS data set that has an audit trail file.

·        INDEX = YES – Copy indexes for indexed data sets that are moved.

You would simply specify these options directly after the MOVE option as in the example, above.

The MOVE option provides a very handy way to have SAS programmatically move files between various data libraries.

Best of luck in all your SAS endeavors!

----MMMMIIIIKKKKEEEE
(aka Michael A. Raithel)

Excerpt from the book:  Did You Know That?  Essential Hacks for Clever SAS Programmers

I plan to post each and every one of the hacks in the book to social media on a weekly basis.  Please pass them along to colleagues who you know would benefit.

Monday, August 22, 2016

Summertime and The Writin’ is Easy


SAS Programming Professionals, 

Summertime and the writin’ is easy!  Clients are on vacation, managers are on vacation, users are on vacation, colleagues are on vacation, and the office is quiet.  Nice and quiet. Maybe just a little too quiet!

In many offices, there is a lull in the programming workload as people enjoy the warmer weather and head out for vacations, three-day weekends, and summer holidays.  The result of this happy exodus is fewer deadlines and less pressure on your work hours during the summer months.  So, what can you do when the programming workload diminishes?

You can outline and write the draft of a SAS technical paper for SAS Global Forum 2017; that’s what you can do!

The SAS Global Forum 2017 Call for Content is open between now and October 17th. 
http://www.sas.com/en_us/events/sas-global-forum/sas-global-forum-2017/program/sessions.html#call-for-content

Next year’s conference is actively looking for content in five formats:

  • Breakout Sessions – Get insights on the in-depth aspects of SAS software, including programming techniques, technical features, and innovative and practical applications. They can also be strategies and best practices about how to apply SAS to solve industry-specific challenges.
     
  • Hands-On Workshops – Get hands-on experience through a demonstration that you direct for 45 minutes. Hands-On Workshops (also known as HOW) are intended to help attendees learn how to use the features of SAS or JMP®.
     
  • Quick Tips – Perfect for beginners and experts alike to share quick tips and tricks, and useful nuggets of programming techniques that make jobs easier. A Quick Tip session should last 10 minutes or less and cover one concept in a concise and practical way
     
  • E-Posters – Here’s a perfect opportunity to present your solution or idea in a more relaxed environment and in a visually informative way. A greener alternative to traditional posters, e-posters allow you to transport digital posters in your pocket
     
  • Table Talks – Facilitated by one lead user in an informal, small-group conversational setting (max. of 20 participants), these sessions will not require a formal paper to be submitted. A limited number of sessions will be selected, and each may be offered multiple times.

Do you have an area of SAS expertise, a great programming methodology, or a clever SAS programming technique that you would like to write about and present?  If so, then compose a draft of the paper during the current programming lull and submit it to SAS Global Forum 2017.
 
If you have not previously submitted a SAS technical paper to a conference, then you may consider following steps: 

  • First determine if you have novel content to offer or a fresh perspective on doing something that is tried-and-true.  The best way to do this is to search previous conference papers to see if your topic has already been covered to excess.  Here is a link to previous years’ SAS Global Forum proceedings:  http://support.sas.com/events/sasglobalforum/previous/online.html
     
  • Thoroughly research your topic using SAS online documentation, previous conference papers, and other types of research papers that apply to your subject matter.  You want to make sure that your own paper covers the most up-to-date facets of the topic you are going to be presenting.
     
  • Create a draft of your paper.  Your draft should have the following sections:
     
    • Abstract – This is a concise paragraph or two that specify the programming “problem” and the solution your paper is presenting.
       
    • Introduction – This section provides a more detailed discussion about the programming problem and your solution.  It also introduces how the content of the body of the paper will be laid out.
       
    • Body of paper – This part is composed of multiple sections that provide the detailed explanations of your programming methodology, code, tip, etc.
       
    • Conclusion -- One or more paragraphs that summarize what has been discussed in the body of the paper.
       
    • Disclaimer – A standard boiler-plate disclaimer concerning the contents of your paper, SAS trademarks, and other trademarks must be included at the end of your paper.  See the author’s guidelines for the latest wording.
       
    • References – A list of references you used in researching your paper.   Your readers can access these references for additional information on the material presented.
       
    • Acknowledgements – A paragraph that thanks the people who helped you or who provided guidance during your research or writing of the paper.
       
    • Contact Information – Of course, you will want people to contact you either to heap praise upon the clever insightfulness of your paper or to ask questions.  So, provide an email address that they can use.
       
  • Submit your proposal to the conference by accessing this link:  http://www.contentet.com/SASGF2017/
     
  • Enjoy having put together a cogent treatment of a SAS topic and anticipate getting good news from the SAS Global Forum 2017 staff in October.

Okay, so the Call for Content does not actually require that an entire draft of your paper be submitted in order for it to be considered.  However, you should take advantage of this respite in your programming workload to get the research and writing heavy lifting done now.  You are not going to have the luxury of having so much time on your hands once the summer is over and everybody is back in their offices.
 
Summertime and the writin’ is easy!  That makes it a perfect time to outline, draft, and submit a paper to SAS Global Forum 2017.
 
Or, of course; you could just go to the beach!
 
Best of luck in all your SAS endeavors!
 
----MMMMIIIIKKKKEEEE
(aka Michael A. Raithel)
http://www.amazon.com/Michael-A.-Raithel/e/B001K8GG90/ref=ntt_dp_epwbk_0

Monday, August 15, 2016

Hack 3.17 Maintaining Relative Order During PROC SORT

SAS Programming Professionals,

Did you know that you can maintain the relative order of observations in a BY group during a sort?  You can do so by using the EQUALS option on the PROC SORT statement. 

This concept is easier to illustrate than it is to describe, so let’s consider the SASHELP.CLASS data set that is already sorted by NAME.


We would like to have the data set sorted by SEX, but keep the NAMEs in alphabetical order within SEX.  So, we would code:

proc sort data=sashelp.class out=class equals;
by sex;
run;

…which nets the following:


Note that the resulting SAS data set is sorted by SEX and that the relative order of NAME (in alphabetical order) has been preserved.  The EQUALS option is handy for when you already have an established relative order within observations that you want to maintain after sorting by the variables in your BY statement.


Best of luck in all your SAS endeavors!

----MMMMIIIIKKKKEEEE
(aka Michael A. Raithel)

Excerpt from the book:  Did You Know That?  Essential Hacks for Clever SAS Programmers

I plan to post each and every one of the hacks in the book to social media on a weekly basis.  Please pass them along to colleagues who you know would benefit.

Friday, August 5, 2016

Hack 3.16 Keeping Track of SAS Data Set Updates with Audit Trails

SAS Programming Professionals,

Did you know that you can have SAS keep an audit trail of all of the updates made to a SAS data set? 

This is particularly handy if you need to determine who updated a SAS data set at any given time for a security review or a data integrity audit.  The audit trail file allows you to track when particular observations were added, modified, or deleted.  You may even be able to fashion your own update roll-back program from the audit file.

Creating an audit trail file for a SAS data set is easily done via the DATASETS Procedure.  Here is an example:

proc datasets library=cperm noprint;
      audit orsales;
      initiate;
run;
quit;

Executing the code, above, results in a SAS audit file named orsales.sas7baud being built in the CPERM SAS data library.  When updates are made to the orsales SAS data set, entries are made to the attendant orsales SAS audit file.

You can determine the variables available in your audit file by using PROC CONTENTS with the TYPE=AUDIT data set option.  Here is an example:

proc contents data=cperm.orsales(type=audit);
run;

The resulting Alphabetic List of Variables and Attributes looks like this:

Alphabetic List of Variables and Attributes
#
Variable
Type
Len
Format
Label
4
Product_Category
Char
25

Product Category
5
Product_Group
Char
25

Product Group
3
Product_Line
Char
20

Product Line
7
Profit
Num
8
12.2
Profit in USD
6
Quantity
Num
8
6.
Number of Items
2
Quarter
Char
6

Quarter
8
Total_Retail_Price
Num
8
12.2
Total Retail Price in USD
1
Year
Num
8
4.
Year
9
_ATDATETIME_
Num
8
DATETIME19.

14
_ATMESSAGE_
Char
8


10
_ATOBSNO_
Num
8


13
_ATOPCODE_
Char
2


11
_ATRETURNCODE_
Num
8


12
_ATUSERID_
Char
32




The variables beginning with _AT are the audit trail variables.  Two of the most important are _ATDATETIME_ and _ATUSERID_ which hold the date/time an observation was updated and the user ID of the person who updated the observation, respectively.

Once a SAS data set with an audit trail is updated, you can examine the specifics of the change by using PROC PRINT with the TYPE=AUDIT data set option:

proc print data=cperm.orsales(type=audit);
run;

You can use other SAS procedures, such as PROC REPORT and PROC SQL to print out the SAS audit file, too.

There is much, much more to discuss on this topic, and the SAS technical writers handle it with their usual thoroughness in the SAS online documentation on support.sas.com.  Check it out!

Best of luck in all your SAS endeavors!

----MMMMIIIIKKKKEEEE
(aka Michael A. Raithel)

Excerpt from the book:  Did You Know That?  Essential Hacks for Clever SAS Programmers

I plan to post each and every one of the hacks in the book to social media on a weekly basis.  Please pass them along to colleagues who you know would benefit.