topic Re: Error when i import doc file into sas.. in SAS Programming

Error when i import doc file into sas..

yaswanthj — Fri, 20 Sep 2013 11:25:02 GMT

Hi All..

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..

my code..

%macro word2sas(in=, out=temp);

options noxwait noxsync;

%let rc=%sysfunc(system(start winword));

data test;

x=sleep(3);

run;

filename word DDE 'winword|System';

data _null_;

file word;

put '[FileOpen.Name = "' "C:\Documents and Settings\yaswanth.j\Desktop\test\example2.doc" '"]';

put "[EditSelectAll]";

put "[EditCopy]";

put '[FileClose]';

put '[AppMinimize]';

run;

%let rc=%sysfunc(system(start excel));

data test;

x=sleep(3);

run;

filename excel dde 'excel|system';

data _null_;

file excel;

put '[error(false)]';

put '[paste]';

put '[SAVE.AS("C:\Documents and Settings\yaswanth.j\Desktop\test\Example2.xls")]';

put '[Select.Last.Cell()]';

put '[Copy]';

put '[quit]';

run;

proc import out=temp datafile= "C:\Documents and Settings\yaswanth.j\Desktop\test\Example2.xls" DBMS=excel replace;

getnames=yes;

mixed=yes;

run;

filename fd "C:\Documents and Settings\yaswanth.j\Desktop\test\Example2.xls";

%let rc=%sysfunc(fdelete(fd));

filename _all_ clear;

%mend word2sas;

%word2sas(in = C:\Documents and Settings\yaswanth.j\Desktop\test\example2.doc, out=temp);

Please help me where can i change the code..

Thanks,

Yash..

Re: Error when i import doc file into sas..

Cynthia_sas — Sat, 21 Sep 2013 01:24:40 GMT

Hi:

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.

cynthia

Re: Error when i import doc file into sas..

yaswanthj — Wed, 25 Sep 2013 10:04:43 GMT

Thanks

Re: Error when i import doc file into sas..

arronlee — Tue, 06 May 2014 07:22:42 GMT

Hi, Yash.

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.

Best regards,

Arron

Re: Error when i import doc file into sas..

Ksharp — Tue, 06 May 2014 12:44:06 GMT

try

DBMS=xls

DBMS=excelcs

Re: Error when i import doc file into sas..

Cynthia_sas — Tue, 06 May 2014 21:10:49 GMT

I do not believe that DBMS=xls or DBMS=excelcs will help with trying to read a WORD doc into SAS.

cynthia

Re: Error when i import doc file into sas..

Ksharp — Wed, 07 May 2014 16:24:08 GMT

Cynthia,

From the OP's log, it is obviously the error is coming from PROC IMPORT which is used to import an excel file .

Right ?

Xia Keshan

Re: Error when i import doc file into sas..

Cynthia_sas — Wed, 07 May 2014 17:36:19 GMT

Hi,

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 IMPORT or the Import Wizard 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.

cynthia