09-20-2013 07:25 AM
I have a word doc file(.doc) in my PC. i want to get that file into sas. this should be automated using macro.. i have below problem to get the file .DOC file to generate the datset in sas automatically. but im getting an issue while generating the .doc file to sas dataset..
in the below program everything is good, but while generating the dataset from .xls it is giving an error like ;
ERROR: Connect: External table is not in the expected format.
ERROR: Error in the LIBNAME statement.
Connection Failed. See log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 2.29 seconds
cpu time 1.04 seconds
Im using SAS V9.1 and windows environment..
%macro word2sas(in=, out=temp);
options noxwait noxsync;
%let rc=%sysfunc(system(start winword));
filename word DDE 'winword|System';
put '[FileOpen.Name = "' "C:\Documents and Settings\yaswanth.j\Desktop\test\example2.doc" '"]';
%let rc=%sysfunc(system(start excel));
filename excel dde 'excel|system';
put '[SAVE.AS("C:\Documents and Settings\yaswanth.j\Desktop\test\Example2.xls")]';
proc import out=temp datafile= "C:\Documents and Settings\yaswanth.j\Desktop\test\Example2.xls" DBMS=excel replace;
filename fd "C:\Documents and Settings\yaswanth.j\Desktop\test\Example2.xls";
filename _all_ clear;
%word2sas(in = C:\Documents and Settings\yaswanth.j\Desktop\test\example2.doc, out=temp);
Please help me where can i change the code..
09-20-2013 09:24 PM
Are you referencing the %WORD2SAS macro program that was described in this 2009 paper? http://www.pharmasug.org/download/papers/CC18.pdf If so, perhaps the paper author could help you. I believe there is contact information at the end of the paper.
05-06-2014 03:22 AM
I wonder have you ever found your way out? Whether you have used some Word Doc SDK to help you with the related projects? Even though I am not so sure about whether it is the best way to slove your problem, I hope you can google it and select a fine Word Doc Toolkit whose way of processing is simple and fast to help you with the related work. It will be better if it is totally manual and can be customized by users according to our own favors. Remember to check its free trial package first if possible. And I hope you success. Good luck.
05-07-2014 12:24 PM
From the OP's log, it is obviously the error is coming from PROC IMPORT which is used to import an excel file .
05-07-2014 01:36 PM
Well, sort of. It seems to me that the %WORD2SAS macro program is copying something from a DOC file and trying to paste it into Excel using DDE and then doing a Save As with DDE commands. And, the %WORD2SAS macro program was written by someone else. If the OP has issues with the macro program and how it works, he/she needs to work with the author of the macro program. And, while changing the macro program's PROC IMPORT step might work, by changing the DBMS= option -- it still seems to me that recommending DMBS=EXCELCS is dependent on the whole bitness issues, and having the PC File server -- which may not have become an issue until SAS 9.2 or 9.3 and the OP said he had 9.1 (but didn't mention his version or bitness of Office).
And, in addition, the OP might have found this Tech Support note 9554 - "External table is not in the expected format" returned when reading Excel file with PROC IMP... if searched on the support.sas.com site. To me the error message implied that the COPY from the Word doc got pasted as some other format of Excel file. So the SAVE with the DDE did not save in the right format needed for PROC IMPORT. So, to me that implies that the SAVE step in the DDE code needs to be changed instead of changing the DBMS= option.
At any rate, since nobody knows what's actually in the Word doc (from the September 2013 post), the other issue could be that the Word doc has more in it than just a simple table, and what's being pasted into Excel is not really of a form to be turned into a sheet in Excel. And, since the whole post took place originally in September last year, and the posting in May 2014 was from somebody entirely different, I wasn't sure that how the May post was even related to the original question. And now, on rereading the May 6th post, it really doesn't add anything to the solution to the original problem from September, except to suggest something about the OP getting a Word SDK -- which wouldn't really help, either, with the %WORD2SAS macro program. But since the original posting seems very over, now on rereading everything, I think that all of our May posts are probably irrelevant and/or moot.
So signing off, without further comment.