SAS import multiple text file in subfolders

[ammarhm]

Hi everyone.

I have a folder that contain mutiple text documents, placed in subfolders.

Is there a way of importing the contents of these files as a string into a dataset?

I have found some examples round the internet and in SAS community but they dont seem to be working.

THe documents are all .doc

Kind regards

[Tom]

What is the question exactly?

Do you want to get the list of files in directory tree?  Or do you also want then then use that list to convert those files into SAS datasets?

If you have an example that can find the files with names that end with .doc then you should be able to modify the program to find other files.

[ammarhm]

It is not only the file names that I need.

What I need is the text in the file documents themselves. 

So the final SAS table I am trying to create would contain 2 columns:

the first is the file name

The second is a string that contains the imported text from the word document

I hope that explained it further

[Patrick]

@ammarhm

Writing up some code which automates accessing files from different folders is very doable and there are already quite a few variations of how to achieve this here in the forums.

A Word document is NOT a simple plain text file so the real question for us to understand is: How does this Word document actually look like and what do you want to read into this 2nd variable.

Even though some members of the forums don't want to download Excel or Word attachments for security reasons, I'd still suggest that you attach a sample Word document and then explain/show us how the expected result should look like (best provided as a SAS datastep creating the desired result set as a SAS table).

You are aware that a SAS variable can only hold 32KB of characters but that a Word document could be much bigger and also contain embedded documents, pictures and many more things which can't get read into SAS variables?

What you're asking for will require a very specific set of Word documents.

So: Let's first get something working for one of your Word docs and only then look into generalizing the task for multiple Word docs in multiple sub-folders.

[ammarhm]

@Patrick @Tom @Reeza

Hi Patrick and Tom

Thank you for the excellent reply, and the remarks included in your post.

I fully agree with everything you said and as you can see in the attached word document, there are multiple word elements included, such as a table, free text and a text box.

I am aware of the limitation in SAS variable size, the documents contain only text and should not exceed the 32KB in size, so that is gnot going to be a problem

I really appreciate your time and help with this.

Kind regards

[Reeza]

SAS doesn't haven an easy way to accomplish this. 

I personally prefer python for this, since you can convert the DOC to DOCX and then there are many packages that will help you parse out the content as desired.  

You don't actually have a limit on the text so you can't guarantee that the fields will smaller than the 32K character limit. You'll need multiple reads to get around that, but it's not the end of the world. 

[ammarhm]

Thank you Reeza
I still believe it is better to use on system rather than two different to achieve the task. I can import the word document using VBA otherwise but i prefer doing the whole thing through SAS though it might not be the optimal approach

[Reeza]

Do you have SAS e-Miner and/or text miner? That will actually process these files for you...

Otherwise, I'm in the camp of using the right tool for the right job. I'd probably write a script to process the word doc and then call the script from SAS.  I understand doing it all in one tool though. 

[ammarhm]

Unfortunately I dont have these packages
I was hoping to do it all through SAS and I have seen some people who managed to do it, but I am not able to implement a solution to this specific case

[Reeza]

Save the file as text and try that. 

SAS could write a VBS file to do the conversion and then process the text file. If you have specific file structure and tags you can use perl expressions to parse it. If there are definitive structures it should be straight forward. But test it first, it should be something that's quick to check. 

[ammarhm]

Thanks Reeza
I still hope someone out there will have a SAS 9nly approach. Implementing command X in SAS is not always the preferred option.thanks again

[Reeza]

Just to be clear about requirements:

These are doc files.

They are in multiple subfolders. 

You cannot uses X commands. This means that you cannot batch convert the files to txt, rtf or docx formats? Docs may be parsed as it's in an XML format. You cannot use DDE because you can't use X commands. 

So you're asking how to use SAS to import a two decade old proprietary unstructured format? Without using the SAS tool specifically designed to handle this type of data? 

[ammarhm]

Thank you Reeza

i understand, I guess I will have to do it with X commands and DDE

But I hope I wont need to use VBA, at least

Kind regards

[Patrick]

@ammarhm

I would be using Tika for this task. Tika can extract text from many different file formats. You then can pipe the result directly into SAS.

Below code that works in my environment:

filename extract pipe 'java -jar "c:\Apache\Lucene\tika17\tika-app-1.7.jar" -t C:\temp\sample.doc';
data want;
  length txt $32767;
  retain txt;

  infile extract end=last;
  input;

  txt=catx(' ',txt,_infile_);
  if last then output;
run;
filename extract clear;

Tika is a great open source tool for text extraction. You can download it from here: https://tika.apache.org/download.html 

SAS is using Tika for some of its product stack and depending on what SAS software/solutions you have licensed, you might be lucky and have Tika already installed (i.e. for SAS Content Categorization).

[ammarhm]

@Patrick

Thank you very much for this approach, I havent actually heard of it before, but reading through the webpage, i see it has immense potentials.

I will definatly look into this and try to use it 

Kind regards