Export SAS dataset to Access with formatted values

Question

I'm generating a table in SAS and exporting it to a Microsoft Access database (mdb). Right now, I do this by connecting to the database as a library:

libname plus "C:\...\plus.mdb";

data plus.groupings;
set groupings;
run;

However, SAS doesn't export the variable formats to the database, so I end up with numeric values in a table that I want to be human-readable. I've tried proc sql with the same effect. What can I do to get the formatted data into Access?

This seems so fundamental that I feel like I must be missing something obvious. Thanks for your help.

What I've tried so far: Plain libname to mdb, data step (as above)
Plain libname to mdb, proc sql create table
OLE DB libname (as in Rob's reference), data step
OLE DB libname, proc sql create table

Answer 1

There's a bunch of alternative connection types here, maybe one of those will work?:

http://support.sas.com/techsup/technote/ts793.pdf

Answer 2

What's working right now:

Because SAS does preserve formatted values in csv, I'm exporting the table to a csv file that feeds a linked table in the Access database. This seems less than ideal, but it works. It's weird that SAS clearly has the capacity to export formatted values, but doesn't seem to document it.

proc export
data= groupings
outfile= "C:\...\groupings.csv" 
dbms= CSV
replace;
putnames= yes;
run;

A seeming disadvantage of this approach is that I have to manually recreate the table if I add a new field. If I could drop/create in proc sql, that wouldn't be an issue.

Answer 3

From SAS support: if you create a SQL view using put() to define the variables, you can then export the view to the database and maintain the formatted values. Example:

libname plus "C:\...\plus.mdb";

proc sql;
create view groupings_view as (
    SELECT put(gender, gender.) AS gender,
           put(race, race.) AS race,
           ... etc.
    FROM groupings
);


create table plus.groupings as (
    SELECT *
    FROM groupings_view
);

quit;

I wasn't able to just create the view directly in Access - it's not entirely clear to me that Jet supports views in a way that SAS understands, so maybe that's the issue. At any rate, the above does the trick for the limited number of variables I need to export. I can imagine automating the writing of such queries with a funky macro working on the output of proc contents, but I'm terribly grateful I don't have to do that...