topic How to short exporting time from SAS to MS Access? in Microsoft Integration with SAS

How to short exporting time from SAS to MS Access?

bbb_NG — Mon, 20 Feb 2012 08:23:51 GMT

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.

How to short exporting time from SAS to MS Access?

LinusH — Mon, 20 Feb 2012 10:13:14 GMT

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

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

How to short exporting time from SAS to MS Access?

bbb_NG — Mon, 20 Feb 2012 10:41:58 GMT

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.

How to short exporting time from SAS to MS Access?

LinusH — Mon, 20 Feb 2012 11:40:16 GMT

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?

How to short exporting time from SAS to MS Access?

art297 — Mon, 20 Feb 2012 14:55:15 GMT

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.

How to short exporting time from SAS to MS Access?

bbb_NG — Tue, 21 Feb 2012 01:54:35 GMT

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.

How to short exporting time from SAS to MS Access?

bbb_NG — Tue, 21 Feb 2012 08:25:09 GMT

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.