Crushing the SAS Certification Exam

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 exams 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

Hack 2.13 Using the ATTRIB Statement to Specify Multiple Attributes

SAS Programming Professionals,

Did you know that you can specify the length, format, informat, and label for a variable all in the same statement?

The aptly named ATTRIB statement lets you specify the aforementioned attributes of a variable in a single statement, obviating the need for separate LENGTH, FORMAT, INFORMAT, and LABEL statements.  Here is an example:

data birthfile;

attrib birthdate length=6  label = "Birth Date" format=worddate.                  informat=mmddyy.;

attrib firstname length=$7 label = "First Name";

attrib lastname  length=$7 label = "Last Name";

input birthdate firstname lastname;

datalines;

042775 Michael Raithel

;

run;

proc print label noobs;

run;

In the example, we use the ATTRIB statement to specify LENGTH, FORMAT, INFORMAT, and LABEL for BIRTHDATE.  We don’t really need a FORMAT or INFORMAT for FIRSTNAME and LASTNAME, so we only specify the LENGTH and LABEL on the second two ATTRIB statements.  The resulting PROC PRINT looks like this:

                         First      Last

        Birth Date       Name       Name

       April 27, 1975    Michael    Raithel

Using an ATTRIB statement is a good alternative to using multiple LENGTH, FORMAT, INFORMAT, and LABEL statements because it specifies all of the attributes of a variable in one spot.  Good documentation!  See if it makes sense in your own SAS programs.

Best of luck in all of your SAS endeavors!

----MMMMIIIIKKKKEEEE

(aka Michael A. Raithel)

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

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

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.

Hack 2.12 Using the ANYDTDTE Informat to Read Any Date Format

SAS Programming Professionals,

Did you know that you can easily input a plethora of very messy date values using the ANYDTDTE informat?  

This very utilitarian SAS informat can read and extract a host of different date values that are found in real-world data.  Here is an example:                                                                       

data datefile;

input oddates ANYDTDTE32.;

format oddates mmddyy10.; 

datalines;

27APR09

27APR2010

27APR95 14:30:08

27APR2009 14:30:08.5

042769

04272001

97004

2003004

042779

04272099

04/69

04-2007

04/27/11 02:30:08 AM

04/27/2012 02:30:08 AM

APR99

APR2000

030427

19870427

s04Q1

1995Q1

;

run;

proc print noobs data=datefile;

run; 

In this contrived example, we are inputting date values that have been stored in 20 different formats.  The ANYDTDTE informat interprets the input text lines as date values and stores them as SAS dates in the ODDATES variable.  Don’t take my word for it; enter this example into a SAS Display Manager session and take it for a test drive.  As you can see, the ANYDTDTE informat is a handy tool to use when you are reading input files with varying or non-traditional date formats.

The ANYDTDTE informat has two sibling informats:  ANYDTTME which reads and extracts time values from various date, time and datetime forms; and ANYDTDTM, which reads and extracts datetime values from various date, time and datetime forms.  They are also worth looking into!

Best of luck in all of your SAS endeavors!

----MMMMIIIIKKKKEEEE

(aka Michael A. Raithel)

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

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

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.

Hack 2.11 Stripping the Formats Off of Every Variable in a SAS® Data Set

SAS Programming Professionals,

Did you know that you can quickly and easily strip the formats off of every variable in a SAS data set?

This is handy in situations where you have a SAS data set without the associated format catalog.  In such instances, it is difficult to open the SAS data set in DATA steps and procedures, and it is difficult to view it in SAS Explorer.  Often, you may simply choose to specify OPTIONS NOFMTERR to preserve the format associations and still be able to work with the data set.  However, if you determine that this is too much trouble and that you really do not want the format associations, you can permanently remove them with the DATASETS Procedure.  

Here is an example:

      /*Allocate SAS libraries*/

      libname ctemp "c:\temp";

      /* Create PRDSAL2 in CTEMP library */

      data ctemp.prdsal2;

      set sashelp.prdsal2;

      run;

/* PROC CONTENTS to show the formats */

proc contents data=ctemp.prdsal2 details;

run;

/* Strip all formats from PRDSAL2 data set*/

proc datasets library=ctemp nolist;

modify prdsal2;

format _all_;

run;

/* PROC CONTENTS to show all formats are gone baby gone!*/

proc contents data=ctemp.prdsal2 details;

run;

The first part of the example is simply setup.  The real action takes place in the DATASETS Procedure.  There, we specify the relevant SAS data library and SAS data set, CTEMP and PRDSAL2, respectively.  Then, the FORMAT statement with _ALL_ strips the formats off of all of the variables in the SAS data set.

I would bet that some of you are wondering what you need to do to strip off the formats of individual variables… right?  That is easy enough:

      proc datasets library=ctemp nolist;

      modify prdsal2;

      format actual country;

      run;

 …will relieve variables ACTUAL and COUNTRY of their formats.  It’s that simple!

Best of luck in all of your SAS endeavors!

----MMMMIIIIKKKKEEEE

(aka Michael A. Raithel)

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

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

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.

Hack 2.10 Starting Windows® Programs from Within a SAS Program

SAS Programming Professionals,

Did you know that you can open other Windows programs from within a SAS program?

You might decide that it would be convenient to open Word, Excel, Windows Explorer, Internet Explorer, or possibly PowerPoint at some time during the execution of a SAS program.  If so, it can be done programmatically using the SYSTEM function.

In this example, we create a CSV file and then open Excel to QC the contents of that file:

ods results off;

ods csv file="c:\temp\classlist.csv";

proc print noobs data=sashelp.class;

run;

ods csv close;

data _null_;
   rc=system("start excel c:\temp\classlist.csv");
run;

You can see from the code that we are using the Output Delivery System to create a CSV file named classlist.csv.  The DATA _NULL_ step uses the SYSTEM command to “START” excel and open classlist.csv.

Here is an example of creating an RTF file and then launching Word to open that file:

options nodate nonumber;

ods results off;

ods rtf file="c:\temp\classlist.rtf";

proc print noobs data=sashelp.class;

title1 "Mrs. Dob's 7th Grade Class Roster";

run;

ods rtf close;


data _null_;

   rc=system("start winword c:\temp\classlist.rtf");

run;

Pretty neat, eh?  Don’t take my word for it; enter these examples into a SAS Display Manager session and take them for a test drive.

You can also use the SYSTEM command in open code via SAS Macro code as in this example that launches my favorite web site:

%let rc=%sysfunc(system(start iexplore http://support.sas.com/index.html));

I would bet that you can find dozens of uses for opening Windows programs with the SYSTEM function in your own SAS programs!

Best of luck in all of your SAS endeavors!

----MMMMIIIIKKKKEEEE

(aka Michael A. Raithel)

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

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

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.

Hack 2.9 Specifying the SYSTASK and WAITFOR Commands to Launch SAS Programs

SAS Programming Professionals,

Did you know that you can launch SAS programs from within a SAS program and suspend execution of the launching program until the launched SAS programs have complete their execution?  

Doing this, you can control the execution sequence of your SAS programs if/when you do not want to depend entirely upon an OS's scheduling software such as the CRON on Linux, the Windows Scheduler on Windows, and so on.

For example, in your BIGPROGRAM.SAS program, you could have the following code: 

systask command "sas your_clever_program1.sas" taskname=program1;

systask command "sas your_clever_program2.sas" taskname=program2; 

waitfor _all_ program1 program2;

<<Other SAS code that processes the data sets created by the two afore-mentioned SAS programs>>

Note that the "_all_" option states that your program is to wait for both program1 and program2 to complete before resuming execution.  As an alternative, you could have coded "_any_", which would have your program resume the moment either program1 or program2 completed execution.

You can probably think of a number of ingenious ways to use SYSTASK and WAITFOR to schedule the specific order for your SAS programs to execute.  So, get started!

Best of luck in all of your SAS endeavors!

----MMMMIIIIKKKKEEEE

(aka Michael A. Raithel)

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

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

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.

Hack 2.8 Sorting Very Large SAS Data Sets Using Views

SAS Programming Professionals,

Did you know that you can sort very large SAS data sets with the judicious use of SAS views?

Once in a while, we all get a SAS data set so large that our computer does not have enough space to sort it.  One way around this problem is to use SAS views to break the original data set into segments that can be sorted separately and then reassembled into a final sorted data set.  An overview of this methodology is:

•  Create several views which subset the data set into segments aligned with a subgroup values of the sort variables.
• Input each of the views into its own PROC SORT and write the output to individual data sets.
• Use the APPEND procedure to append the sorted data sets into one large sorted data set.
• Use the DATASETS procedure to assert the sort for the final SAS data set.
   Here is an example which uses the SASHELP.CLASS data set.  Yes, I do realize SASHELP.CLASS only has 19 observations, but it serves our overall purposes for illustrating the methodology.
  
  

              /*Create a data set that is not sorted by first name */

      proc sort data=sashelp.class out=work.class;

           by weight;

      run;

/* Create Views based on first letter of first name */

data class1 / view=class1;

set work.class; 

    where "A" <= substr(name,1,1) <= "G";

run;

data class2 / view=class2;

set work.class;

    where "H" <= substr(name,1,1) <= "R"; 

run;

data class3 / view=class3;

set work.class; 

    where "S" <= substr(name,1,1) <= "Z"; 

run;

/* Sort views out to SAS data sets*/

proc sort data=class1 out=class1_sorted;

     by name;

run;

proc sort data=class2 out=class2_sorted;

     by name;

run;

proc sort data=class3 out=class3_sorted;

     by name;

run;

/* Apppend sorted data sets and clean up after yourself*/

proc append base=class_sorted_by_name data=class1_sorted;

run;

proc append base=class_sorted_by_name data=class2_sorted;

run; 

proc append base =class_sorted_by_name data=class3_sorted;

run;

proc datasets library=work nolist; 

     modify class_sorted_by_name(sortedby= name);

     run;

     delete class1_sorted class2_sorted class3_sorted;

     delete class1(memtype=view) class2(memtype=view)

       class3(memtype=view);

quit;

The first thing we do is create a data set to use for this example.  We sort the CLASS data set by age to make sure it is not sorted by NAME.  Now, we are ready to go.

The first real step in this methodology is to create three DATA step views.  Each one of the views constructs a subset of the original SAS data set  by using a logical sequence of the key variable value we will be using to sort the data set.  (The number of observations returned by all three DATA step views will be equal to the number of observations in the original data set).  Once the views are created, each one is input to a sort.  The SORT procedure inputs the view; which in turn only feeds the related portion of the original data set into the sort.  That portion of the original data set is sorted by NAME and output to its own sorted data set.

We continue by using PROC APPEND to concatenate the sorted segments of our SAS data set together into a new SAS data set.  Finally, we use the DATASETS procedure to specify that our final SAS data set is sorted by NAME.  And, we are careful to clean up those messy, large, intermediate SAS data sets by deleting them.  For good measure, we delete the views too.

This is obviously a simplistic example.  But, a clever person such as yourself can doubtlessly extend it to sort the impossibly large real-world SAS data sets that drift into your own work-life!

Best of luck in all of your SAS endeavors!

----MMMMIIIIKKKKEEEE

(aka Michael A. Raithel)

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

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

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.