SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

How to short exporting time from SAS to MS Access?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 99
Accepted Solution

How to short exporting time from SAS to MS Access?

Hi,

     I have a work to export SAS data file to MS Access mdb file.

     since the original sas file is of 300M, it will takes hours to do the transmission.

     Thus I have to do it after work every time,

     can anyone give a better solution?

     the source file has been put

     options compress=yes.

     Thanks in advance.


Accepted Solutions
Solution
‎02-21-2012 03:25 AM
Frequent Contributor
Posts: 99

How to short exporting time from SAS to MS Access?

Dear All,

I solve the problem by exporting from SAS using TXT instead of MDB format. It's much quicker even adding the time you have to import the txt file into mdb.

View solution in original post


All Replies
Esteemed Advisor
Posts: 5,198

How to short exporting time from SAS to MS Access?

See my comments and others in other thread(s).

http://communities.sas.com/message/112532#112532

Data never sleeps
Frequent Contributor
Posts: 99

How to short exporting time from SAS to MS Access?

LinusH,

I've read that article.

First let me explain why I should export from SAS to access.

I have to use excel pivot table and due to the records exceeds 65000, it should be stored in ms access.

and excel pivot table seems doesn't  allow read from sas.

and in the topic you refered to, I didn't find a useful way to short the time.

anyway, thanks for your reply.

Esteemed Advisor
Posts: 5,198

How to short exporting time from SAS to MS Access?

Excel allows you to read from SAS, but it is your SAS license that may hinder this.

To read SAS data you need license for either SAS/SHARE and SAS/SHARE*NET, or have a BI Server license, which allows for quite tight integration between SAS and MS Office.

You don't have any access to any SQL Server database? My guess it wold load much faster, espaciallay if ou can use OLEDB. But even export your SAs tabel to a textfile and importing it by DB routine would bestaer than you current Access load.

Compress=YES will probably have the I/O reduced, but to a cost of increased CPU activity.

To test if which would be most efficient for you do a:

data _null_;

     set have;

run;

Both on a compressed and uncompressed input data.

Do you need to export the whole table each time? Perhaps you could develop a method to just modify/add changed records?

Data never sleeps
Esteemed Advisor
Posts: 7,293

How to short exporting time from SAS to MS Access?

I'm confused.  Since you have more than 65000 records, and that is why you feel that you have to go to Access before Excel, I presume that you are on an older version of Excel.

Why not do all of your data summarizations in SAS rather than Access?  If you can, you would have a much smaller file to transfer.

Frequent Contributor
Posts: 99

How to short exporting time from SAS to MS Access?

Art297,LinusH,

     Thank you both.

LinusH, as you said "To read SAS data you need license for either SAS/SHARE and SAS/SHARE*NET, or have a BI Server license, which allows for quite tight integration between SAS and MS Office."

can you give me an article to read it?

     Why I should convert sas data to access to excel, because the ending user only know how to use pivot table.

     and thank you for mention the later treatment.

     For each month going on, i will generate only one month data then use update function in MS access,which will be much faster.But for the time being, I am debug my whole project, which need whole exporting, untolerable.

     Very thanks for sharing your thoughts.

     I hope I will catch up and start to help others soon.

Solution
‎02-21-2012 03:25 AM
Frequent Contributor
Posts: 99

How to short exporting time from SAS to MS Access?

Dear All,

I solve the problem by exporting from SAS using TXT instead of MDB format. It's much quicker even adding the time you have to import the txt file into mdb.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 721 views
  • 3 likes
  • 3 in conversation