topic Re: SAS dataset export to a foxpro DBF in SAS Programming

SAS dataset export to a foxpro DBF

overky — Mon, 10 Apr 2017 14:45:34 GMT

I am exporting a dataset to DBF. The export works but there are decimals in every variable.

PROC EXPORT DATA=Test1
outfile='F:\\hq2sso19.dbf'
DBMS=DBFMEMO REPLACE;
RUN;

Is it possible to set a variable type in the export or is this a procedure before exporting? I need to export as numerica fields no decimals for Foxpro Version 8.0.

Thanks

overky

Re: SAS dataset export to a foxpro DBF

ballardw — Mon, 10 Apr 2017 17:47:58 GMT

I doubt that you will have any luck with Proc Export. Export is very limited in options and none control output formats.

You have a couple choices. One if your license for SAS is correct would be to use Proc DBLOAD to write the file direct to the dbf file. That allows setting formats. Here is what an example from the documentation looks like:

PROC DBLOAD DBMS=DBFMEMO DATA=employee;
  PATH='c:\sasdemo\employee.dbf';
  RENAME firstname = fname;
  TYPE   empid     = 'numeric(6)'
         hiredate  = 'date'
         salary    = 'numeric(10,2)'
         jobcode   = 'numeric(5)';
  RUN;

Though DBLOAD seems to imply DBase not Foxpro

Another possible option would be to use the Proc Export to write a CSV file and control options on inporting that file into the DBF.

Re: SAS dataset export to a foxpro DBF

overky — Mon, 10 Apr 2017 17:59:16 GMT

When I try that code, I get the following:

This DBLOAD compatibility procedure does not support full screen features.

Also will this statement accept a replace command so that the DBF file is overwritten each time the SAS is executed?

Re: SAS dataset export to a foxpro DBF

Tom — Mon, 10 Apr 2017 20:22:29 GMT

Do your variables have formats attached? I am pretty sure that PROC EXPORT to DBF format will use the format attached to your variable when deciding how to create the field in the DBF file.

Re: SAS dataset export to a foxpro DBF

overky — Mon, 10 Apr 2017 20:24:55 GMT

Not that I am aware of.

My code reads in a txt file with a DLM of "\"

Takes that dataset and then needs to export as a dbf.

Re: SAS dataset export to a foxpro DBF

Tom — Mon, 10 Apr 2017 20:28:49 GMT

So define formats for your numbers and see what happens.

data XXX ;
   infile .... .;
   input .... ;
   format AGE 3. ;
run;

proc export ....

Re: SAS dataset export to a foxpro DBF

overky — Mon, 10 Apr 2017 20:48:05 GMT

I am not sure I am explaining very well...

I have:

500.00

600.00

700.00

But I need

500

600

700

I need my dbf fields without decimals my dataset does not have decimals but when I export to a dbf it creates decimals.

Re: SAS dataset export to a foxpro DBF

Tom — Mon, 10 Apr 2017 20:54:58 GMT

Have in what sense? If you have a SAS dataset then you have IEEE 8 byte floating point numbers. In that situation there is no difference between 700.00 and 700 since they are both seven hundred.

Perhaps you mean that you currently have attached a format like 6.2 to your variable so that when it prints you always see two digits to the right of the decimal place.

data have ;
  input x ;
  format x 6.2 ;
cards;
500
600
700
;

In that case attach a DIFFERENT format so that PROC EXPORT knows not to include the decimal places.

data to_export;
  set have ;
  format x 3. ;
run;
proc export data=to_export out='myfoxpro.dbf' dbms=dbf;
run;