Date:         Tue, 16 Dec 2008 10:42:19 -0600
Reply-To:     Mary <mlhoward@avalon.net>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mary <mlhoward@AVALON.NET>
Subject:      Re: excel 2007 with filters and proc import
Comments: To: "Halbert, Carolyn" <chalbert@OAG.STATE.VA.US>
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
              reply-type=original

No, Excel 2007 is not compatible with SAS 9.1.3.   Here is an include that
data _null_ wrote
to write out a Excel file as a tab-delimited file and then you can import
the tab-delimited file
into SAS; I'm not sure what happens with filtered worksheets; but I would
guess that the
entire worksheet would be written out.

-Mary

/*
Add to calling program:
filename workbook "C:\test.xlsx";
filename txtout "C:\Work_Activities\folder\";
%include 'C:\thisfile.txt';
*/

data _null_ / pgm=work.sheets2txt;
/* gather info */
length workbook txtout script filevar command $256;
workbook = pathname('WORKBOOK');
txtout = pathname('TXTOUT');
script = catx('\',pathname('WORK'),'SHEETS2TXT.vbs');
filevar = script;
/* write the script */
file dummy1 filevar=filevar;
put 'Const ' workbook=$quote256.;
put 'Const ' txtout=$quote256.;
put 'Set objExcel = CreateObject("Excel.Application")';
put +3 'With objExcel';
put +3 '.Visible = True';
put +3 '.DisplayAlerts = False';
put +3 'Set objWorkbook = .Workbooks.Open(workbook)';
put +3 'i = 0';
put +3 'Set colSheets = .WorkSheets';
put +3 'For Each objSheet In colSheets';
put +6 'i = i + 1';
put +6 'WScript.echo i & " " & objsheet.name ';
put +6 'objSheet.SaveAs txtout & "\" & objSheet.name & ".txt",21';
put +6 'Next';
put +3 '.Application.Quit';
put +3 'End With';
/* close the script file by opening another, not used */
filevar = catx('\',pathname('WORK'),'DUMMY.vbs');;
file dummy1 filevar=filevar;
/* look at the script, not necessary but may be useful */
infile dummy2 filevar=script end=eof;
do until(eof);
input;
putlog _infile_;
end;
/* call the script */
command = catx(' ','cscript //NoLogo',quote(strip(script)));
infile dummy3 pipe filevar=command end=eof;
do until(eof);
input;
putlog _infile_;
end;
stop;
run;


data pgm=work.sheets2txt;
run;
filename workbook clear;
filename txtout clear;

----- Original Message -----
From: Halbert, Carolyn
To: SAS-L@LISTSERV.UGA.EDU
Sent: Tuesday, December 16, 2008 7:15 AM
Subject: excel 2007 with filters and proc import


When importing a heavily filtered file in excel 2007 to sas I kept
getting these errors:

PROC IMPORT DATAFILE="c:\pcsas_import_files\sealant.xls"

out=t_0 replace;

/*error statement indicated dbms type excel2007

not valid for import when done using excel 2000 dbms and xls not xlsx
suffix

 file does not exist tried as csv file indicates is not valid or out of
order

will not let me save as excel 2003 without 'extreme loss of
functionality*/

   SHEET="sealant";

   GETNAMES=YES;

RUN;


I fixed this by importing as a CSV file but lost all the work I did in
filters.  Can sas 9.1.3 take an imported excel 07 file (especially
filtered) and if so can you share some code that will take care of this?

Thanks