How to write a .dbf file and specify its format

[WesBarris]

I would like to output a sas dataset to a .dbf file.  I also would like to specify the field format for the columns of data in the .dbf file.  The dbf procedure documentation seems to imply that I can do this with a format statement.  I've tried the following syntax:

LIBNAME mylib 'u:\';

FILENAME test 'u:\test.dbf';

PROC DBF DB3=test DATA=wbhat;

FORMAT wb $6. mg 3.0 age 1.0;

RUN;

This code successfully creates a .dbf file but the field width of the character variable (wb) is 5 characters, not the 6 that I specified.  Also, the numeric fields are 16 digits wide, not the 3 and 1 that I specified.  Is it possible to specify the column field widths of a .dbf file created from sas?

[Tom]

You cannot use a FORMAT statement to change the length of the character variables.  You can change the length by using a data step or proc sql statement to create the variable with a different length.

You cannot change the 16 spaces that SAS uses to store the numeric fields.

Here is the link to the SAS 9.2 documentation:

http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003087278.htm

Converting SAS Variables to DBF Fields

Numeric variables are stored in character form by DBF files. SAS numeric variables become numeric variables with a length of 16 when converted from a DBF file. SAS decimal values must be stored in a decimal format to be converted to a DBF decimal value. Associate the SAS numeric variable with an appropriate decimal format. The corresponding DBF field does not have any value to the right of the decimal point. You can associate a format with SAS variables when you create the data set or with the DATASETS procedure.

If the number of digits including a possible decimal point exceeds 16, a warning message is issued and the DBF numeric field is filled with the character 9 . All SAS character variables become DBF fields of the same length. When you convert a SAS data set to a DBF file that is compatible with dBASE III or later, SAS date variables become DBF date fields. When you convert a SAS data set to a dBASE II file, SAS date variables become dBASE II character fields in the form YYYYMMDD.

[WesBarris]

Thanks Tom.  That is the exact documentation that I was refering to.  With poorly structured sentences like "Associate the SAS numeric variable with an appropriate decimal format." it is difficult to understand what they are trying to say.

So, it sounds like from your response that there is a way to change character fields but the integer fields will always be 16 in a dbf file created by sas.  Is that correct?

I am a beginning sas user so examples really help.

[Tom]

To change the length of a variable you need to create a new dataset.

data new;

   length wb $6 ;

   set wbhat ;

run;

Do you really care if the DBF file has numeric or character variables?  You could convert AGE to $1 using a PUT function call.

data new;

    set wbhat (rename=(age = numeric_age) );

    age = put(age,1.) ;

    drop numeric_age ;

run;

If you are more comfortable with SQL syntax then use PROC SQL.

proc sql ;

  create table new as

     select wb as wb length=6

              , put(age,1.) as age length=1

             , put(mg,3.) as mg length=3

      from wbhat

  ;

quit;

[Ksharp]

Or

data class;set sashelp.class;run;
proc sql noprint;
 alter table class
   modify sex char(20);
quit;