topic Re: SAS import multiple text file in subfolders in SAS Programming

SAS import multiple text file in subfolders

ammarhm — Sat, 24 Jun 2017 12:42:31 GMT

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

Re: SAS import multiple text file in subfolders

Tom — Sat, 24 Jun 2017 12:51:48 GMT

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.

Re: SAS import multiple text file in subfolders

ammarhm — Sat, 24 Jun 2017 12:54:25 GMT

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

Re: SAS import multiple text file in subfolders

Patrick — Sat, 24 Jun 2017 15:13:29 GMT

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

Re: SAS import multiple text file in subfolders

ammarhm — Sun, 25 Jun 2017 02:04:50 GMT

@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

Re: SAS import multiple text file in subfolders

Reeza — Sun, 25 Jun 2017 02:54:21 GMT

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.

Re: SAS import multiple text file in subfolders

ammarhm — Sun, 25 Jun 2017 03:54:38 GMT

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

Re: SAS import multiple text file in subfolders

Reeza — Sun, 25 Jun 2017 03:57:10 GMT

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.

Re: SAS import multiple text file in subfolders

ammarhm — Sun, 25 Jun 2017 04:03:36 GMT

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

Re: SAS import multiple text file in subfolders

Reeza — Sun, 25 Jun 2017 05:59:38 GMT

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.

Re: SAS import multiple text file in subfolders

ammarhm — Sun, 25 Jun 2017 06:33:18 GMT

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

Re: SAS import multiple text file in subfolders

Reeza — Sun, 25 Jun 2017 07:02:50 GMT

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?

Re: SAS import multiple text file in subfolders

ammarhm — Sun, 25 Jun 2017 08:20:42 GMT

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

Re: SAS import multiple text file in subfolders

Patrick — Sun, 25 Jun 2017 11:21:50 GMT

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

Re: SAS import multiple text file in subfolders

ammarhm — Sun, 25 Jun 2017 10:42:27 GMT

@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

Re: SAS import multiple text file in subfolders

ammarhm — Sun, 25 Jun 2017 13:24:33 GMT

so it seems using X command or Tika are the two available options.

Any other sggestions please from other users with experience i similar cases

@Ksharp any alternative approaches please?

Thanks

Re: SAS import multiple text file in subfolders

Reeza — Sun, 25 Jun 2017 16:08:43 GMT

If you have SAS IML you could see if there's an R package to read a doc file

https://communities.sas.com/t5/General-SAS-Programming/Run-R-code-inside-SAS-easily/td-p/210116

Re: SAS import multiple text file in subfolders

Ksharp — Mon, 26 Jun 2017 13:11:58 GMT

Yes. I think SAS can do that. 1)use " dir c:\temp\*.txt /s /b " command to get all the path of file. 2)After that use FILENAME + filevar= to get what you want . @Tom is specaility for that .

Re: SAS import multiple text file in subfolders

Reeza — Mon, 26 Jun 2017 14:39:06 GMT

@Ksharp wrote:
Yes. I think SAS can do that. 1)use " dir c:\temp\*.txt /s /b " command to get all the path of file. 2)After that use FILENAME + filevar= to get what you want . @Tom is specaility for that .

@Ksharp DOC files return gibberish if you connect to them via a filename the last time I tried. I'd be happy to be proven wrong though.

Re: SAS import multiple text file in subfolders

Ksharp — Tue, 27 Jun 2017 12:02:30 GMT

Opps. I thought OP want import TEXT files .