BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bbb_NG
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
bbb_NG
Fluorite | Level 6

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

6 REPLIES 6
LinusH
Tourmaline | Level 20

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

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

Data never sleeps
bbb_NG
Fluorite | Level 6

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.

LinusH
Tourmaline | Level 20

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
art297
Opal | Level 21

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.

bbb_NG
Fluorite | Level 6

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.

bbb_NG
Fluorite | Level 6

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.

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!

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