topic Re: SAS/Access not reading MS Access ShortText properly in SAS Programming

SAS/Access not reading MS Access ShortText properly

JacquesR — Tue, 15 Oct 2024 00:11:20 GMT

Hi all

Sample code won't really help, but for what it's worth:

PROC IMPORT
    OUT=WORK.Test
    TABLE="Info"
    DBMS=ACCESS
    REPLACE;
    DATABASE="<path>\<dataset>.accdb";
RUN;

My problem is this.

I have a large amount of importing to do (about 20 datasets, 6 tables per dataset). The code above is nested in a macro which does the import quite nicely.

but...

The table Info contains three fields all using the ShortText data type, using the default length of 255.

SAS is aware of MS Access data types:

https://support.sas.com/documentation/cdl/en/acpcref/63181/HTML/default/viewer.htm#p0psac3j16cioen1nq2hkwrnk55y.htm

These three fields, however, have many empty rows, and some rows with text, sometimes quite long text.

When SAS does the import, it is importing these three columns as very short ($3 or $4) based on the length of the first non-empty row, instead of using the full column width ($255).

If these were csv files, I could use guessingrows=max, but that is not an option with the Access files.

I have looked through the various SAS options for DBMS=ACCESS (see link above) but while there are options to control how dates and times are imported, no such options exist for ShortText. Also, there are many ShortText fields, but it seems to be reasonably uniquely these three that SAS is truncating.

Any ideas as to how I can fix it, without having to export each of those databases manually?

Re: SAS/Access not reading MS Access ShortText properly

JacquesR — Tue, 15 Oct 2024 01:08:35 GMT

I'd still be interested to hear suggestions, but I realised I could use this approach for my macro instead:

Create a SAS/ACCESS LIBNAME to the database.

Read the table (using SAS name literals) and write it to a SAS dataset using PROC SQL.

A quick test shows that these variables are now reading properly from at least one dataset when using the LIBNAME.

I may post some basic code when I am done.

Re: SAS/Access not reading MS Access ShortText properly

Patrick — Tue, 15 Oct 2024 01:48:40 GMT

Using Proc Import have you tried DBSASTYPE? I believe syntax similar to below could work.

PROC IMPORT 
    DATAFILE="C:\path\to\your\database.accdb" 
    OUT=work.mydata 
    DBMS=ACCESS REPLACE;
    TABLE="YourTableName";
    DBSASTYPE=('ShortTextColumn'='CHAR(255)');
RUN;

Re: SAS/Access not reading MS Access ShortText properly

Ksharp — Tue, 15 Oct 2024 02:12:01 GMT

Patrick,
Should like this:

proc import datafile = "c:\temp\date.xlsx" out =have replace dbms =excel ;
dbdsopts="dbsastype=(age='numeric' weight='char(20)' weight='char(20)')";
run;

libname x excel 'c:\temp\date.xlsx';
data have2;
set x.'date$'n(dbsastype=(age='numeric' weight='char(20)' weight='char(20)'));
run;

Re: SAS/Access not reading MS Access ShortText properly

Tom — Tue, 15 Oct 2024 02:24:28 GMT

You seem to have some good suggestions for how to tell PROC IMPORT what SAS types to use for the variables.

But the old SAS 9.3 documentation you linked also says there is a SCANMEMO option of the SAS/Access to MS Access files. Setting that to YES will tell it to scan all of the memo fields and set the length to longest string, instead of just setting it to the first value. You might try using that option in your macro and see if PROC IMPORT can make better GUESSES about what length to use for those variables.

Re: SAS/Access not reading MS Access ShortText properly

JacquesR — Tue, 15 Oct 2024 03:41:07 GMT

Tom, the documentation notes:

Restrictions

SCANMEMO= applies only to MEMO data type columns; it does not apply to TEXT type columns.

Re: SAS/Access not reading MS Access ShortText properly

JacquesR — Tue, 15 Oct 2024 03:46:01 GMT

Thanks @Patrick and @Ksharp

That works. Annoying that the documentation page I linked to does not mention DBSASTYPE.

For what it's worth, this is what the Import statement in the macro looks like now, and it works. So that has spared me rewriting the macro to use the LIBNAME with PROC SQL.

    PROC IMPORT
        OUT=WORK.D_&newtable._&n.
        TABLE="&table."
        DBMS=ACCESS
        REPLACE;
        %IF &newtable. EQ DDI_Info %THEN %DO;
    		DBDSOPTS="DBSASTYPE=('Drug_Factors'='CHAR(255)' 'Patient_Factors'='CHAR(255)' 'Clinical_Factors'='CHAR(255)')"; 
		%END;
        DATABASE=&dfile.;
    RUN;

Re: SAS/Access not reading MS Access ShortText properly

Patrick — Tue, 15 Oct 2024 05:10:21 GMT

@JacquesR wrote:

Thanks @Patrick and @Ksharp

That works. Annoying that the documentation page I linked to does not mention DBSASTYPE.

I've used already multiple times the docu feedback button when I felt something is missing and several times my proposed changes actually made it into the docu.

I also started to use AI assistance (mostly Copilot) when looking for solutions. Not that the answers are always correct but it's often giving me new ideas.

If you want to test it with Copilot use below prompts and see what you get:

proc import ms access short text to SAS character variable length 255

...or even more fun: Just copy/paste your initial question into Copilot or similar.