Use a Microsoft Excel file to create a user-defined format

A document that discusses Use a Microsoft Excel file to create a user-defined format

/*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';

   column name height;

   define name / group f=$testfmt.;

   define height / mean 'Average Height' f=6.2;

run;

 

ods html close;

ods listing ;