topic how can i export a sas data set with 444 columns into access? The limit is 255 columns. in SAS Data Management

how can i export a sas data set with 444 columns into access? The limit is 255 columns.

Maicemuallem — Thu, 03 Mar 2016 19:50:49 GMT

how can i export a sas data set with 444 columns into access? The limit is 255 columns.

Re: how can i export a sas data set with 444 columns into access? The limit is 255 columns.

LinusH — Thu, 03 Mar 2016 20:03:23 GMT

That's an Access limitation, right?

If so, there's nothing much SAS can do about that, technically.

You should consider your data structure. How can you even handle that many columns. Think of naming all those columns in a query etc...

Transpose and normalize your data. It will make it easier to use and maintain. Ans as a bonus, being able to export to Access 🙂

Re: how can i export a sas data set with 444 columns into access? The limit is 255 columns.

Reeza — Thu, 03 Mar 2016 20:09:39 GMT

Access 2010 - 255 columns in a table.

https://support.office.com/en-us/article/Access-2010-specifications-1e521481-7f9a-46f7-8ed9-ea9dff1fa854

Separate data into two tables that can be linked by an ID. Each table holding less than 255 columns.

Re: how can i export a sas data set with 444 columns into access? The limit is 255 columns.

RW9 — Fri, 04 Mar 2016 09:35:40 GMT

As @LinusH has stated, structure your data correctly. Access is (a rather poor sibling to anyway), a Relational Database. What this means is that data elements only appear once, these distinct lists as simple to merge back together but are not stored in one table. Attached is a picture which shows an example. You will see that the data is split out into separate tables, each with links to other tables. This is what "relational" means, once they are merged together by the variables then you get the full data.

There are many reasons for this setup. I would suggest you read up on what a Relation Database is, and how your data should look:

https://en.wikipedia.org/wiki/Relational_database

For example as a starter.

Then plan out your database schema -> much like the picture attached, draw each table, what distinct values it will contain, how they link together, then its just a matter of processing your one dataset in SAS to create each of the required datasets, which in turn can then be inserted into the respective table in Access. Note that SAS is not a Relational Database, so the structure is slighty different.