Date:         Fri, 9 Oct 1998 08:58:25 -0500
Reply-To:     "Boylan, Dave" <dboylan@UTILICORP.COM>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         "Boylan, Dave" <dboylan@UTILICORP.COM>
Subject:      Re: Excel to SAS via PROC ACCESS
Comments: cc: "jxb@BELLATLANTIC.NET" <jxb@BELLATLANTIC.NET>
Content-Type: text/plain

Jules,
 
If Victor is right, you may want to try using ODBC to get at your Excel-97
data.  The SAS version shouldn't matter since Microsoft provides the
Excel-97 ODBC driver.
 
> ----------
> From:         Victor Kamensky[SMTP:kamensky@AECOM.YU.EDU]
> Reply To:     Victor Kamensky
> Sent:         Friday, October 09, 1998 8:18 AM
> To:   SAS-L@VM.MARIST.EDU
> Subject:      Re: Excel to SAS via PROC ACCESS
>
> Jules!
> I do not think that's the reason for the error message below.
> The reason is EXCEL version.
> I bet you use EXCEL 97.
> I got the same error trying to convert EXCEL 97 into SAS.
> But the same code works for Excel 5 for both DBMS=XLS and DBMS=EXCEL.
> Looks like PROC ACCESS cannot read EXCEL 97 now, but will in
> Version 7.
> The only thing now is to output your file as Excel 5 in Excel
> and use your macro to convert the Excel 5 into SAS.
> Victor Kamensky
> Programmer
> Albert Einstein College of Medicine
>
> At 09:09 AM 10/9/98 -0400, you wrote:
> >Jules;
> >
> >The DBMS= should be XLS, not EXCEL.
> >
> >+++++++++++++++++++++++++++++++++++++++++++++++
> >Edward Heaton, Systems Development Specialist,
> >ACS Government Solutions Group, Inc.
> >(a division of Affiliated Computer Services, Inc.),
> >on contract at U.S. Department of Labor, Room C-3514,
> >200 Constitution Avenue, Northwest, Washington, DC 20210
> >(202) 693-0886               mailto:edheaton@fenix2.dol-esa.gov
> >http://www.acs-inc.com/index.html
> >http://www2.dol.gov/dol/esa/public/owcp_org.htm
> >+++++++++++++++++++++++++++++++++++++++++++++++
> >
> >
> >-----Original Message-----
> >From:   Automatic digest processor [SMTP:LISTSERV@UGA.CC.UGA.EDU]
> >Sent:   Friday, October 09, 1998 12:00 AM
> >To:     Recipients of SAS-L digests
> >Subject:        SAS-L Digest - 8 Oct 1998 to 9 Oct 1998
> >
> >Date:    Thu, 8 Oct 1998 18:00:53 -0400
> >From:    Jules Bosch <jxb@BELLATLANTIC.NET>
> >Subject: Excel to SAS via PROC ACCESS
> >
> >Hi All,
> >A copy of the SAS log follows for a problem that just occurred.  Any help
> would
> >be greatly be appreciated.
> >
> >Jules Bosch
> >
> >1    /* Imp_xls.sas */
> >2    /* This SAS pgm reads data from Excel files into SAS data sets */
> >3
> >4       dm 'clear log'
> >4                     ; dm 'clear out'
> >4                                     ;
> >5       options nocenter pageno=1;
> >6
> >7       %macro imp_xls (xfile,get_name,wdsn);
> >8          PROC ACCESS DBMS=EXCEL;
> >9          CREATE WORK._IMEX_.ACCESS;
> >10   *     PATH="u:\aaa-hcs\data\&xfile..xls";
> >11   *     PATH="c:\jnj_hcs\data\&xfile..xls";
> >12         PATH='c:\jnj_hcs\data\dps9971.xls';
> >13         GETNAMES &get_name;  /* YES or NO */
> >14         SCANTYPE=YES;
> >15         CREATE WORK._IMEX_.VIEW;
> >16         SELECT ALL;
> >17         RUN;
> >18         DATA WORK.&wdsn;
> >19         SET WORK._IMEX_;
> >20         RUN;
> >21         PROC DATASETS LIBRARY=WORK MEMTYPE=ACCESS NOLIST;
> >22         DELETE _IMEX_;
> >23         QUIT;
> >24         PROC DATASETS LIBRARY=WORK MEMTYPE=VIEW NOLIST;
> >25         DELETE _IMEX_;
> >26         QUIT;
> >27
> >28      /* Print the imported Excel file */
> >29         title1 "Excel file &xfile";
> >30         proc print;   run;
> >31
> >32      %mend;
> >33
> >34      %*imp_xls(pgi4q97,no,pgi4q97);
> >35
> >36      %imp_xls(dps9971,yes,dps9971);
> >
> >ERROR: Database error. Examine fields and/or messages below.
> >Unexpected OLE2 file manipulation error (-2147287038).
> >NOTE: Statement ignored, not valid for creating ACCESS descriptors.
> >
> >NOTE: The PROCEDURE ACCESS used 0.48 seconds.
> >
> >
> >ERROR: File WORK._IMEX_.DATA does not exist.
> >
> >
>