BookmarkSubscribeRSS Feed
WesBarris
Obsidian | Level 7

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?

4 REPLIES 4
Tom
Super User Tom
Super User

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
Obsidian | Level 7

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
Super User Tom
Super User

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
Super User

Or

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1867 views
  • 0 likes
  • 3 in conversation