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.
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 ;
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 | | |
