BookmarkSubscribeRSS Feed
Maicemuallem
Calcite | Level 5

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

3 REPLIES 3
LinusH
Tourmaline | Level 20

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
Reeza
Super User

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. 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 942 views
  • 6 likes
  • 4 in conversation