Sample 37283: Use a Microsoft Excel file to create a user-defined format
You can easily generate a user-defined format if you have an existing Microsoft Excel spreadsheet containing variables and descriptions.
This sample uses Base SAS® to convert an Excel file into a SAS data set and modifies it for use by PROC FORMAT as a control data set. The CNTLIN data set must contain three required variables: FMTNAME, START, and LABEL.
For another example of building a user-defined format from a SAS data set, see
Example 5: Creating a Format from a Data Set in the Base SAS 9.2 Procedures Guide.
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.
You can use values from your Excel file to create a format that can be used by SAS procedures. After converting the file into a SAS data set using PROC IMPORT, modify it to create the variables PROC FORMAT expects in a control data set. In the example below, we are using the resulting format in a PROC REPORT step to display average height by gender.
/*Create an Excel spreadsheet for the example. */
filename test 'c:\testfmt.csv';
proc export data=sashelp.class outfile=test
dbms=csv replace;
run;
/* Read the Excel spreadsheet and create a SAS data set. */
proc import datafile=test out=work.testfmt dbms=csv replace;
run;
/* Build control data set */
data new(drop=sex);
retain fmtname 'testfmt' type 'C';
length label $6.;
set testfmt;
rename name=start;
if sex='M' then label='Male';
else label='Female';
run;
/* Create the format using the control data set. */
proc format cntlin=new;
run;
/* Use new format to display average height by gender */
title;
ods listing close;
ods html file="c:\myfile.htm" style=styles.meadow;
proc report data=sashelp.class nowd;
title 'Using Control Data Set to generate a format with Excel file';
col name height;
define name / group f=$testfmt.;
define height / mean 'Average Height' f=6.2;
run;
ods html close;
ods listing ;
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.
If you have an existing Excel spreadsheet containing variables and descriptions, you can easily transform it into a custom format.
| Type: | Sample |
| Topic: | Common Programming Tasks ==> Reading and Writing External Data ==> with PROC EXPORT
Common Programming Tasks ==> Reading and Writing External Data ==> with PROC IMPORT
Common Programming Tasks ==> Reading and Writing SAS Data
Common Programming Tasks ==> Selecting Data
Common Programming Tasks ==> Working with Character Data
Common Programming Tasks
|
| Date Modified: | 2009-10-13 15:14:41 |
| Date Created: | 2009-09-28 14:53:09 |
Operating System and Release Information
| SAS System | Base SAS | Windows Vista | | |
| Microsoft Windows XP Professional | | |
| Windows Millennium Edition (Me) | | |
| Microsoft Windows Server 2008 | | |
| Microsoft Windows Server 2003 Standard Edition | | |
| Microsoft Windows Server 2003 Enterprise Edition | | |
| Microsoft Windows Server 2003 Datacenter Edition | | |
| Microsoft Windows NT Workstation | | |
| Microsoft Windows 2000 Professional | | |
| Microsoft Windows 2000 Server | | |
| Microsoft Windows 2000 Advanced Server | | |
| Microsoft Windows 2000 Datacenter Server | | |
| Microsoft Windows 95/98 | | |
| OS/2 | | |
| Microsoft Windows XP 64-bit Edition | | |
| Microsoft® Windows® for x64 | | |
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | | |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | | |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | | |
