SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
New Contributor
Posts: 4

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

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

Super User
Posts: 5,429

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

Posted in reply to Maicemuallem

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 :-)

Data never sleeps
Super User
Posts: 19,815

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

[ Edited ]

Access 2010 - 255 columns in a table.

 

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

 

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

 

Super User
Super User
Posts: 7,961

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

Posted in reply to Maicemuallem

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.


access.png
Ask a Question
Discussion stats
  • 3 replies
  • 279 views
  • 6 likes
  • 4 in conversation