Sample 39648: How to create a report using PROC REPORT to show all the possible variable values
 |  |  |  |  |  |
The sample code on the Full Code tab illustrates a technique for creating a report to show all possible values of a variable even when the input data set does not contain all of the values.
The first DATA step creates data set Allvalues, containing all of the possible values of a variable.
The second DATA step uses the values of the data set Allvalues to create the CNTLIN data set My_format_input for the PROC FORMAT step. The Start and Label variables are assigned the variable name that contains all of the possible values; in this example, the variable name is Colors. The Fmtname variable contains the format name of $MYFMT. The dollar sign ($) is added to the format name to indicate that the format is character. Another way to indicate that the format is character is to define a variable named Type and assign it the value of 'C'. At a minimum, the CNTLIN data set needs to have the Start, Label, and Fmtname variables defined.
The CNTLIN= option uses the newly created data set of My_format_input as input to PROC FORMAT. The temporary format of $MYFMT is created and stored in the format catalog in the Work library. To create a permanent format, use the LIBRARY=libref option on the PROC FORMAT statement.
The next step is to create a subset data set, Mysubset, that can be used as input to PROC REPORT. The Color variable will contain the subset values.
PROC REPORT has the ability to use a format value to populate rows or columns with data that might not be in the input data set. The COMPLETEROWS option is added to the PROC REPORT statement to indicate that all of the values are desired in the output report. The PRELOADFMT option along with the format of $MYFMT is added to the DEFINE statement of the variable Color. The PRELOADFMT option will only work with GROUP or ACROSS variables. The N statistic is added to the COLUMN statement. A value of zero for the N statistic indicates that the row value was not part of the subset.
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
/* Create a data set containing all the possible values. */
data allvalues;
input colors $;
datalines;
yellow
green
purple
pink
orange
red
blue
brown
gray
tan
rose
;
run;
/* Create the input data set for the format. */
/* Start is equal to the original values. */
/* Label in this case is equal to Colors. */
/* The format is character, so start the */
/* format name with a $ followed by a name. */
data my_format_input;
set allvalues;
start=colors;
label=colors;
fmtname='$myfmt';
run;
/* Use the CNTLIN option to create the format. */
/* This format will contain all possible values. */
proc format cntlin=my_format_input;
run;
/* Create the subset data set. */
data mysubset;
color='yellow';
output;
color='red';
output;
color='purple';
output;
run;
/* Use the PRELOADFMT option to display all possible values. */
/* Specify the N statistic to calculate the frequency count. */
proc report nowd data=mysubset completerows missing;
col color n;
define color / group preloadfmt format=$myfmt.;
run;
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
PROC REPORT output shows all possible values of a variable. The zero in the N column indicates a zero frequency for those values that were not in the input data set to PROC REPORT.
color n blue 0 brown 0 gray 0 green 0 orange 0 pink 0 purple 1 red 1 rose 0 tan 0 yellow 1
| Type: | Sample |
| Topic: | SAS Reference ==> Procedures ==> REPORT |
| Date Modified: | 2010-08-31 10:45:30 |
| Date Created: | 2010-05-14 14:23:37 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | Microsoft Windows Server 2003 Enterprise Edition | ||
| Microsoft Windows Server 2003 Datacenter Edition | ||||
| Microsoft Windows NT Workstation | ||||
| Microsoft Windows 2000 Server | ||||
| Microsoft Windows 2000 Professional | ||||
| Microsoft Windows 2000 Datacenter Server | ||||
| z/OS | ||||
| OpenVMS VAX | ||||
| Microsoft Windows 95/98 | ||||
| Microsoft Windows 2000 Advanced Server | ||||
| Microsoft® Windows® for x64 | ||||
| OS/2 | ||||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | ||||
| Microsoft Windows XP 64-bit Edition | ||||
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | ||||
| Microsoft® Windows® for 64-Bit Itanium-based Systems | ||||
| Microsoft Windows Server 2003 Standard Edition | ||||
| Microsoft Windows Server 2008 | ||||
| Microsoft Windows XP Professional | ||||
| Windows 7 Enterprise 32 bit | ||||
| Windows 7 Enterprise x64 | ||||
| Windows 7 Home Premium 32 bit | ||||
| Windows 7 Home Premium x64 | ||||
| Windows 7 Professional 32 bit | ||||
| Windows 7 Professional x64 | ||||
| Windows 7 Ultimate 32 bit | ||||
| Windows 7 Ultimate x64 | ||||
| Windows Millennium Edition (Me) | ||||
| Windows Vista | ||||
| 64-bit Enabled AIX | ||||
| 64-bit Enabled HP-UX | ||||
| 64-bit Enabled Solaris | ||||
| ABI+ for Intel Architecture | ||||
| AIX | ||||
| HP-UX | ||||
| HP-UX IPF | ||||
| IRIX | ||||
| Linux | ||||
| Linux for x64 | ||||
| Linux on Itanium | ||||
| OpenVMS Alpha | ||||
| OpenVMS on HP Integrity | ||||
| Solaris | ||||
| Solaris for x64 | ||||
| Tru64 UNIX | ||||