Hack 3.8 Deriving Summary Statistics with PROC SQL

SAS Programming Professionals,

Did you know that you can use PROC SQL to quickly derive summary statistics from SAS data sets? 

You can then use those summary statistics in subsequent DATA steps and procedures.  Here is an example:

 proc sql noprint;

        select count(name), avg(age), min(height), max(height),sum(weight)

        into   :totalobs,   :avgage,  :minheight, :maxheight,   :totweight

        from sashelp.class;

quit;

%put "Number of observations = " &totalobs;

%put "Average age = " &avgage;

%put "Minimum height = " &minheight;

%put "Maximum height = " &maxheight;

%put "Total weight of class = " &totweight;

data class_stats;

set  sashelp.class;

format percent_weight percent.;

percent_weight = weight/&totweight;

abovemin_height = height - &minheight;

belowmax_height = &maxheight - height;

if age > &avgage then older = "Yes";

        else older = "No ";

run;

proc print noobs data=class_stats;

title1 "Selected Class Comparisons";

run;

In the SQL Procedure above, we specify various summary statistics for five variables and store them INTO macro variables.  (The NOPRINT options keeps the List destination clean).  Next, we print the values of the macro variables to the SAS log to document the summary statistics.  Then, we use the newly minted macro variables in a DATA step to derive the values of four new variables.

The first portion of the output looks like this:

                         Selected Class Comparisons

                                    percent_    abovemin_    belowmax_

Name    Sex  Age  Height  Weight     weight       height       height     older

Alfred   M    14   69.0    112.5       6%          17.7          3.0       Yes

Alice    F    13   56.5     84.0       4%           5.2         15.5       No

Barbara  F    13   65.3     98.0       5%          14.0          6.7       No

Carol    F    14   62.8    102.5       5%          11.5          9.2       Yes

Copy the example and take it for a test drive to see how easy it is to derive summary statistics with PROC SQL.

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

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.

Free RUN Statements for Whoever Wants Them

SAS Programming Professionals,

This weekend, I went through a bunch of old SAS programs to see which ones I could delete, which ones were still relevant, and which ones I could salvage code snippets from.  In doing so, I ended up recovering about a dozen RUN statements.  They are of various sizes, fonts, and colors, and are yours for the taking if you can make good use of them.  I also reclaimed several QUIT statements from PROC DATASETS and PROC SQL’s that are similarly up for grabs.

Here they are:

Access this link for more information on the RUN statement: http://tinyurl.com/hgkwzza  

BTW, I make no claim that the code you insert these old statements into will run more efficiently or produce more accurate results.  So, you are basically using these RUN and QUIT statements at your own risk.

Hey, you wouldn’t happen to have any old OPTIONS statements lying around that you don’t need, do you?

Best of luck in all of your SAS endeavors!

----MMMMIIIIKKKKEEEE

(aka Michael A. Raithel)

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

Hack 3.7 Creating More Readable PROC FREQ’s

SAS Programming Professionals,

Did you know that you can create less busy looking frequencies by specifying a few options in PROC FREQ?

The NOROW, NOCOL, and NOPERCENT options on the TABLES statement remove row percents, column percents, and row percentages, respectively.  They can be useful when such information is not really necessary for a given report.  Let’s look at an example of how each of these options work.

The following program:

proc freq data=sashelp.class;

    tables sex * age;

run;

…produces this output:

Sex       Age

Frequency|

Percent  |

Row Pct  |

Col Pct  |      11|      12|      13|      14|      15|      16|  Total

---------+--------+--------+--------+--------+--------+--------+

F        |      1 |      2 |      2 |      2 |      2 |      0 |      9

|   5.26 |  10.53 |  10.53 |  10.53 |  10.53 |   0.00 |  47.37 |    

|  11.11 |  22.22 |  22.22 |  22.22 |  22.22 |   0.00 |        |    

|  50.00 |  40.00 |  66.67 |  50.00 |  50.00 |   0.00 |        |    

---------+--------+--------+--------+--------+--------+--------+

M        |      1 |      3 |      1 |      2 |      2 |      1 |     10

|   5.26 |  15.79 |   5.26 |  10.53 |  10.53 |   5.26 |  52.63 |    

|  10.00 |  30.00 |  10.00 |  20.00 |  20.00 |  10.00 |        |    

|  50.00 |  60.00 |  33.33 |  50.00 |  50.00 | 100.00 |        |    

---------+--------+--------+--------+--------+--------+--------+

Total           2        5        3        4        4        1       19

     10.53    26.32    15.79    21.05    21.05     5.26   100.00

If we add the NOROW option:

proc freq data=sashelp.class;

    tables sex * age / norow;

run;

…we get the following:

Sex       Age

Frequency|

Percent  |

Col Pct  |      11|      12|      13|      14|      15|      16|  Total

---------+--------+--------+--------+--------+--------+--------+

F        |      1 |      2 |      2 |      2 |      2 |      0 |      9

|   5.26 |  10.53 |  10.53 |  10.53 |  10.53 |   0.00 |  47.37 |    

|  50.00 |  40.00 |  66.67 |  50.00 |  50.00 |   0.00 |        |    

---------+--------+--------+--------+--------+--------+--------+

M        |      1 |      3 |      1 |      2 |      2 |      1 |     10

|   5.26 |  15.79 |   5.26 |  10.53 |  10.53 |   5.26 |  52.63 |    

|  50.00 |  60.00 |  33.33 |  50.00 |  50.00 | 100.00 |        |    

---------+--------+--------+--------+--------+--------+--------+

Total           2        5        3        4        4        1       19

     10.53    26.32    15.79    21.05    21.05     5.26   100.00

If we add the NOCOL option:

proc freq data=sashelp.class;

    tables sex * age / norow nocol;

run;

…we get this:

Sex       Age

Frequency|

Percent  |      11|      12|      13|      14|      15|      16|  Total

---------+--------+--------+--------+--------+--------+--------+

F        |      1 |      2 |      2 |      2 |      2 |      0 |      9

|   5.26 |  10.53 |  10.53 |  10.53 |  10.53 |   0.00 |  47.37 |    

---------+--------+--------+--------+--------+--------+--------+

M        |      1 |      3 |      1 |      2 |      2 |      1 |     10

|   5.26 |  15.79 |   5.26 |  10.53 |  10.53 |   5.26 |  52.63 |    

---------+--------+--------+--------+--------+--------+--------+

Total           2        5        3        4        4        1       19

     10.53    26.32    15.79    21.05    21.05     5.26   100.00

If we add the NOPERCENT option:

proc freq data=sashelp.class;

    tables sex * age / norow nocol nopercent;

run;

…we produce the following:

Sex       Age

Frequency|      11|      12|      13|      14|      15|      16|  Total

---------+--------+--------+--------+--------+--------+--------+

F        |      1 |      2 |      2 |      2 |      2 |      0 |      9

---------+--------+--------+--------+--------+--------+--------+

M        |      1 |      3 |      1 |      2 |      2 |      1 |     10

---------+--------+--------+--------+--------+--------+--------+

Total           2        5        3        4        4        1       19

The NOROW, NOCOL, and NOPERCENT options on the TABLES statement can be used to create leaner, cleaner reports from the FREQUENCY procedure when the various percentages do not add to the value of the information you are presenting to your client.  And, we do want to keep our clients happy, don’t we!

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

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 3.6 Creating Empty SAS Data Sets with Same Attributes As Existing Ones

SAS Programming Professionals, 

Did you know that you can easily create a new, empty SAS data set with exactly the same attributes as an existing one? 

The LIKE clause allows you to do this in a simple PROC SQL step:
      
      proc sql;

      create table work.cars_table

            like sashelp.cars;

quit;

 …which produces the following log entry: 

1    proc sql;

2        create table work.cars_table

3            like sashelp.cars;

NOTE: Table WORK.CARS_TABLE created, with 0 rows and 15 columns.

4    quit;

Now, we can use PROC APPEND or PROC SQL or… whatever to populate the new cars_table SAS data set.

If you do not want all of the variables in the old SAS data set to be created in the new one, you can use DROP or KEEP statements to screen them out.  Here is an example: 

proc sql;

      create table work.cars_table(drop=Type Origin DriveTrain)

           like sashelp.cars;

quit;

…producing this SAS log entry:

8    proc sql;

9        create table work.cars_table(drop=Type Origin DriveTrain)

10           like sashelp.cars;

NOTE: Table WORK.CARS_TABLE created, with 0 rows and 12 columns.

11   quit;

…since we dropped the variables: Type, Origin, and DriveTrain. 

This method of cloning an existing SAS data set can save you time lots of time since you will not have to code variable names, lengths, labels, formats, informats, etc.

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

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 3.5 Creating a PROC EXPORT or PROC IMPORT Template Program

SAS Programming Professionals,

Did you know that you don't have to memorize the exact syntax and all of the options of PROC EXPORT or PROC IMPORT, or go fumbling for the documentation every time you need to use them? 

Instead, create a PROC EXPORT template and a PROC IMPORT template for yourself using the wizards in SAS Display Manager.  Then, copy either template into your programs and modify the code to suit the particular needs of the export or import situation at hand.

In SAS Display Manager, select: <File>, then <Export Data>.  Choose the SASHELP.CLASS SAS data set just to have something to work with.  Then, go through all of the subsequent windows, choosing each and every one of the available options.  When you get to the window that states:

"The EXPORT wizard can create a file containing PROC EXPORT commands that can be used in SAS programs to export this data again.  If you want these statements to be generated, enter the filename where they should be saved."

…choose a directory and then a meaningful program name, such as "Export to Excel Template.sas".  When you click <Finish>, you will have a SAS program that looks something like this:

PROC EXPORT DATA= SASHELP.CLASS

     OUTFILE= "C:\Documents and Settings\RAITHEL_M\SAS Class.XLS"

     DBMS=EXCEL LABEL REPLACE;

     RANGE="SAS Class";

     NEWFILE=YES;

RUN;

You can reverse this process with the Import Data wizard to create a PROC IMPORT template.  Now how hard is that?  A little pre-planning now, may save you a lot of time later!

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

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 3.4 Creating a Detailed Directory Listing of Files in A SAS Data Library

SAS Programming Professionals,

Did you know that you can get a detailed directory listing of the files in a SAS data library without having to print all of the detail information for each and every SAS data set in it? 

The listing is called "Library Members" and contains the following information: 

·        Name - Name of the SAS data set, Index, Catalog, etc.

·        Member Type - Data, Index, Catalog, etc.

·        Level - If the SAS data library is composed of concatenated directories, this is the concatenation level at which this file can be found

·        Obs, Entries, or Indexes - The number of observations for a SAS data set, entries for a SAS catalog, and indexes for an index file

·        Vars - Number of variables for a SAS data set

·        Label - The file’s label

·        File Size - Size of the file

·        Last Modified - Date-time stamp of when the file was last modified

This is great information to have if you need to conduct a quick visual inspection of a SAS data library you are not familiar with.

Here is code to produce a Library Members listing:

proc contents data=sashelp._all_ memtype=data details nods;

run;

The DETAILS option enables the more detailed "Library Members" listing and the NODS option turns off that pesky listing of individual data set details.

Because of its size, it is not really practical to print the output to the PROC CONTENTS above in this blog.  Copy it into a SAS program and execute it to see the wealth of information that is available about the members of a SAS data library.

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

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.