Date: Thu, 10 Jun 2010 18:51:06 -0400
Reply-To: Arthur Tabachneck <art297@NETSCAPE.NET>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Arthur Tabachneck <art297@NETSCAPE.NET>
Subject: Re: Proc Export to Microsoft Access Database: Poor Performance
Kevin,
Yes, the code I originally proposed only created another SAS file. The
following code produces an Access table and, as far as I know, via the
passthru method.
However, it raises an interesting question: are the time concerns a SAS
problem or simply a problem of exceeding the limits of Access.
You have a faster system than I do, as the proc export took almost 15
minutes on my system .. and the sql passthru method only shaved a few
minutes off of that. However, with the code shown below, the passthru
method only took 4 minutes with half the records, and only 1 minute with
one-quarter of the records (even though it was adding a table to the now
larger database).
FWIW, I had to comment out the disconnect statement for, if I didn't, I
received an error.
Art
%let WorkPath=%sysfunc(pathname(WORK,L));
%let mdbFile=&WorkPath\CR_temp.mdb;
%let max=100;/*127*/
data OldColors;
do r=0 to &max;
do g=0 to &max;
do b=0 to &max;
freq=1; output;
end;
end;
end;
run;
libname db access datasrc="&mdbFile.";
PROC SQL;
connect to access as db (path="&mdbFile.");
create table db.NewColors as
select * from OldColors;
*disconnect from access;
quit;
libname db clear;
%let max=80;/*127*/
data OldColors;
do r=0 to &max;
do g=0 to &max;
do b=0 to &max;
freq=1; output;
end;
end;
end;
run;
libname db access datasrc="&mdbFile.";
PROC SQL;
connect to access as db (path="&mdbFile.");
create table db.MoreColors as
select * from OldColors;
*disconnect from access;
quit;
libname db clear;
--------
On Tue, 8 Jun 2010 21:31:43 -0500, Kevin Myers <KevinMyers@AUSTIN.RR.COM>
wrote:
>P.S. - I've used passthru code many times in the past, but never to
create a
>table in an external database using data from a SAS data set. I'm not
sure
>offhand what the syntax for that would be, or if it is even possible via
>passthru code...?
>
>----- Original Message -----
>From: "Kevin Myers" <KevinMyers@AUSTIN.RR.COM>
>To: <SAS-L@LISTSERV.UGA.EDU>
>Sent: Tuesday, June 08, 2010 21:24
>Subject: Re: Proc Export to Microsoft Access Database: Poor Performance
>
>
>> Art -
>>
>> Thanks for the suggestion, but unless I'm missing something, your code
>> there
>> just created another SAS data set, rather than creating the intended
table
>> in MS Access, unfortunately...
>>
>> s/KAM
>>
>>
>> ----- Original Message -----
>> From: "Arthur Tabachneck" <art297@NETSCAPE.NET>
>> To: <SAS-L@LISTSERV.UGA.EDU>
>> Sent: Tuesday, June 08, 2010 21:15
>> Subject: Re: Proc Export to Microsoft Access Database: Poor Performance
>>
>>
>>> Kevin,
>>>
>>> Back to your original question, have you tried to create the file via
sql
>>> passthrough? The following took just over a second on my machine
(while
>>> your original code took about 14 minutes on my machine .. SAS 9.1.3 on
a
>>> 4-
>>> processor Windows Server 2003 environment):
>>>
>>> proc sql noprint;
>>> connect to access as db (path="&mdbFile.");
>>> create table NewColors as
>>> select *
>>> from OldColors
>>> ;
>>> quit;
>>>
>>> HTH,
>>> Art
>>> ---------
>>> On Tue, 8 Jun 2010 12:58:21 -0400, Viel, Kevin <kviel@SJHA.ORG> wrote:
>>>
>>>>Avi-Gil may also need to check the options?
>>>>
>>>>16 Proc Options option = ValidVarName ;
>>>>17 run ;
>>>>
>>>>SAS (r) Proprietary Software Release 9.2 TS2M0
>>>>
>>>>VALIDVARNAME=V7 Control the type of SAS variable names that can be
>>> created during a SAS session
>>>>NOTE: PROCEDURE OPTIONS used (Total process time):
>>>> real time 0.00 seconds
>>>> cpu time 0.00 seconds
>>>>
>>>>
>>>>18
>>>>19 Data _null_ ;
>>>>20 "Valid Var Name"n = "V7" ;
>>>>ERROR: The name Valid Var Name is not a valid SAS name.
>>>>21 Put "Valid Var Name"n = ;
>>>>ERROR: The name Valid Var Name is not a valid SAS name.
>>>>22 Run ;
>>>>
>>>>NOTE: The SAS System stopped processing this step because of errors.
>>>>NOTE: DATA statement used (Total process time):
>>>> real time 0.00 seconds
>>>> cpu time 0.00 seconds
>>>>
>>>>
>>>>23
>>>>24 Options ValidVarName = Any ;
>>>>25
>>>>26 Data _null_ ;
>>>>27 "Valid Var Name"n = "Any" ;
>>>>28 Put "Valid Var Name"n = ;
>>>>29 Run ;
>>>>
>>>>'Valid Var Name'n=Any
>>>>
>>>>
>>>>-Kevin
>>>>
>>>>Kevin Viel, PhD
>>>>Senior Research Statistician
>>>>Patient Safety & Quality
>>>>International College of Robotic Surgery
>>>>Saint Joseph's Translational Research Institute
>>>>
>>>>Saint Joseph's Hospital
>>>>5671 Peachtree Dunwoody Road, NE, Suite 330
>>>>Atlanta, GA 30342
>>>>
>>>>(678) 843-6076: Direct Phone
>>>>(678) 843-6153: Facsimile
>>>>(404) 558-1364: Mobile
>>>>kviel@sjha.org
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>NOTICE: This e-mail message and all attachments transmitted with it may
>>> contain legally privileged and confidential information intended solely
>>> for the use of the addressee. In addition, this correspondence may
>>> contain
>>> private patient information protected under the federal privacy rule,
45
>>> C.F.R. Parts 160 and 164, and applicable state law. Unauthorized use or
>>> disclosure of this information is strictly prohibited. If the reader of
>>> this message is not the intended recipient, you are hereby notified
that
>>> any reading, dissemination, distribution, copying or other use of this
>>> message or its attachments is strictly prohibited. If you have received
>>> this message in error, please notify the sender immediately by return
e-
>>> mail or at the telephone number above and delete the original message
and
>>> all copies and backups thereof. Thank you
>>>>
>>>>
>>>>> -----Original Message-----
>>>>> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Yu
>>>>> Zhang
>>>>> Sent: Tuesday, June 08, 2010 12:42 PM
>>>>> To: SAS-L@LISTSERV.UGA.EDU
>>>>> Subject: Re: Proc Export to Microsoft Access Database: Poor
Performance
>>>>>
>>>>> can you try 'xxx xxxx'n.
>>>>>
>>>>> data mytable;
>>>>> set x.'xxxx xxx'n;
>>>>> run;
>>>>>
>>>>> hth
>>>>>
>>>>> Yu
>>>>>
>>>>> On Tue, Jun 8, 2010 at 11:35 AM, Avi-Gil Chaitovsky <
>>>>> avi-gil.chaitovsky@anthem.com> wrote:
>>>>>
>>>>> > I tried using the code below (libname x access 'myfile.mdb';) to
get
>>> to
>>>>> > one of my access databases. However, the tables in that database
all
>>> (?)
>>>>> > have names that include spaces. Now that I have a libname, how
can I
>>>>> > refer to my tables?
>>>>> >
>>>>> > Avi-Gil Chaitovsky
>>>>> > WellPoint, Inc.
>>>>> > Empire BlueCross BlueShield
>>>>> > HealthCare Analytics
>>>>> > Business Information Analyst II
>>>>> > Avi-Gil.Chaitovsky@anthem.com
>>>>> > (212) 476-1483
>>>>> >
>>>>> >
>>>>> > On Sun, 6 Jun 2010 09:48:55 -0700, Jack Hamilton
>>>>> <jfh@STANFORDALUMNI.ORG>
>>>>> > wrote:
>>>>> >
>>>>> > >Access to PC File Formats would be:
>>>>> > >
>>>>> > > libname x access 'myfile.mdb';
>>>>> > >
>>>>> > >I've never used it, because I don't run production programs on
>>> Windows.
>>>>> > >
>>>>> > >--
>>>>> > >Jack Hamilton
>>>>> > >jfh@alumni.stanford.org
>>>>> > >Caelum non animum mutant qui trans mare currunt.
>>>>> > >
>>>>> > >
>>>>> > >
>>>>> > >
>>>>> > >On Jun 6, 2010, at 8:40 , Kevin Myers wrote:
>>>>> > >
>>>>> > >> All three of the following appear to perform very similarly (8
to
>>> 10
>>>>> > minutes execution time, with minimal CPU load):
>>>>> > >> PROC EXPORT (is this using Access to PC File Formats?)
>>>>> > >> Data step writing output table to database via Access to OLE DB
>>>>> libname.
>>>>> > >> Data step writing output table to database via Access to ODBC
>>>>> libname.
>>>>> > >>
>>>>> > >> Other suggestions?
>>>>> > >>
>>>>> > >> Thanks,
>>>>> > >> Kevin M.
>>>>> > >>
>>>>> > >>
>>>>> > >> ----- Original Message ----- From: "Jack Hamilton"
>>>>> > <jfh@alumni.stanford.org>
>>>>> > >> To: "Kevin Myers" <kmyers1@CLEARWIRE.NET>
>>>>> > >> Cc: <SAS-L@LISTSERV.UGA.EDU>
>>>>> > >> Sent: Sunday, June 06, 2010 10:29
>>>>> > >> Subject: Re: [SAS-L] Proc Export to Microsoft Access Database:
>>>>> > >> Poor
>>>>> > Performance
>>>>> > >>
>>>>> > >>
>>>>> > >> If you have SAS/Access to PC Files, SAS/Access to ODBC, or
>>> SAS/Access
>>>>> > to OLE DB available, how do they compare in speed?
>>>>> > >>
>>>>> > >>
>>>>> > >> --
>>>>> > >> Jack Hamilton
>>>>> > >> jfh@alumni.stanford.org
>>>>> > >> Caelum non animum mutant qui trans mare currunt.
>>>>> > >>
>>>>> > >>
>>>>> > >>
>>>>> > >>
>>>>> > >> On Jun 6, 2010, at 6:15 , Kevin Myers wrote:
>>>>> > >>
>>>>> > >>> I need to efficiently populate a Microsoft Access database with
>>> data
>>>>> > from a
>>>>> > >>> SAS table. The example code listed below requires 8 to 10
>>>>> > >>> minutes
>>>>> to
>>>>> > load a
>>>>> > >>> single table containing approximately 2 million rows with 4
>>> numeric
>>>>> > columns
>>>>> > >>> on my machine (SAS 8.2 and Office 2000 under Windows XP SP3
with
>>> all
>>>>> > >>> critical updates). I have tried several variations with no
>>>>> significant
>>>>> > >>> improvement. For comparison, it takes less than 10 seconds to
>>>>> create
>>>>> > the
>>>>> > >>> equivalent SAS dataset. Is there some performance optimization
>>>>> > >>> or
>>>>> > >>> alternative approach that I am overlooking? Note that there
are
>>> NO
>>>>> > indexes
>>>>> > >>> or other complicating factors affecting the Microsoft Access
>>>>> > >>> table
>>>>> > that is
>>>>> > >>> being created.
>>>>> > >>>
>>>>> > >>> Thanks,
>>>>> > >>> Kevin M.
>>>>> > >>>
>>>>> > >>> %let WorkPath=%sysfunc(pathname(WORK,L));
>>>>> > >>> %let mdbFile=&WorkPath\CR_temp.mdb;
>>>>> > >>> %let max=127;
>>>>> > >>> ?
>>>>> > >>> data OldColors;
>>>>> > >>> do r=0 to &max;
>>>>> > >>> do g=0 to &max;
>>>>> > >>> do b=0 to &max;
>>>>> > >>> freq=1; output;
>>>>> > >>> end;
>>>>> > >>> end;
>>>>> > >>> end;
>>>>> > >>> run;
>>>>> > >>> ?
>>>>> > >>> proc export data=OldColors outtable="OldColors" dbms=access
>>> replace;
>>>>> > >>> database="&mdbFile";
>>>>> > >>> run;
>>>>> > >>
>>>>> >
>>>>Confidentiality Notice:
>>>>This e-mail, including any attachments is the
>>>>property of Catholic Health East and is intended
>>>>for the sole use of the intended recipient(s).
>>>>It may contain information that is privileged and
>>>>confidential. Any unauthorized review, use,
>>>>disclosure, or distribution is prohibited. If you are
>>>>not the intended recipient, please delete this message, and
>>>>reply to the sender regarding the error in a separate email.
>>>
>>
| 
