Input multiple text files into my EG project

[VicBrookes]

I was wondering if anyone could help I'm still new to sas and getting the hang of the basics.

i have text files in the following file structure. 

File 1

date 1 date 2 etc etc

text doc 1 text doc 2 text doc 3

all my text files are delimited with | and are all the same format.

how would I enter all my txt files into one query within sas enterprise guide please? 

Any example code would be a great help. But I'm afraid you may have to dumb it down for me to be able to understand as I am so new to this. 

Thank you

[TomKari]

There are two options.

If you want to use the "point and click" capabilities of Enterprise Guide, use the "import data" task to get your data into SAS dataset format.

If you prefer to write a SAS program, you would open a Program window and write data step code. The SAS documentation covers this process in detail.

Tom

[VicBrookes]

Thank you. I understand the point and click method but this will not
bring in all the muti files that are sat below my file structure.

I do need to write the code but I would not know where to start as my
second layer down change names as they are named the date the file was
created and a similar issue with the txt files themselves as they are
named date and time of file being written, plus a unique customer
identifier. I've seen code that uses wildcards but not at a 3 layer
approach.

I do have something above these files currently that brings them into an
excel file but with the amount of data I have this is a very timely
process and I still have to txt to columns after I've brought it in. I
would like my process to be quicker going forward.

Current Excel VBA code



ub Button1_Click()

'

Dim fso As Object 'FileSystemObject

Dim fldStart As Object 'Folder

Dim fld As Object 'Folder

Dim fl As Object 'File

Dim Mask As String



Application.ScreenUpdating = False

Dim newWS As Worksheet



Set newWS = Sheets.Add(before:=Sheets(1))



Set fso = CreateObject("scripting.FileSystemObject") ' late binding

'Set fso = New FileSystemObject 'or use early binding (also replace
Object types)



Set fldStart = fso.GetFolder("FILEPATH") ' <-- use your FileDialog
code here

Mask = "*.txt"

'Debug.Print fldStart.Path & "\"

ListFiles fldStart, Mask

For Each fld In fldStart.SubFolders

ListFiles fld, Mask

ListFolders fld, Mask

Next





Dim myWB As Workbook, WB As Workbook

Set myWB = ThisWorkbook

Dim L As Long, t As Long, i As Long

L = myWB.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row

t = 1

For i = 1 To L

Workbooks.OpenText Filename:=myWB.Sheets(1).Cells(i, 1).Value,
DataType:=xlDelimited, Tab:=True

Set WB = ActiveWorkbook

WB.Sheets(1).UsedRange.Copy newWS.Cells(t, 2)

t = myWB.Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row + 1

WB.Close False

Next

myWB.Sheets(1).Columns(1).Delete

Application.ScreenUpdating = True

End Sub







Sub ListFolders(fldStart As Object, Mask As String)

Dim fld As Object 'Folder

For Each fld In fldStart.SubFolders

'Debug.Print fld.Path & "\"

ListFiles fld, Mask

ListFolders fld, Mask

Next

End Sub

##- Please type your reply above this line. Simple formatting, no

[TomKari]

A couple of questions:

Are you trying to read every file in a directory, or is there a structure with one directory above another, etc, with the files in a set of directories at the bottom.

Do you know the directory names and file names in advance, or do you need the program to discover them?

It is really easy to create and use directory and file names as macro variables, like in this example:

%let DirName = C:\Desktop;

%let FileName = TestFile.txt;

data TestData;

infile "&DirName.\&FileName.";

input a b c;

run;

[Reeza]

I wrote an article on this a while back. It should help you get started. If you have specific questions post back with what you've tried. 

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-tex...

[VicBrookes]

Arrrrrh it was your article I read and tried to work through hence the term "wild card" I really am so new to this sad thing I come from a non developer back ground learn all my stuff from you tube and forums to get my project that far in excel and really am at the stage of leading to walk and your code it taking me in to the marathon. But I'm embracing learning a whole new skill.

I've got this far and willing to learn so if I try again over the next few days and provide you with where I'm going wrong your help would be amazing. 

[TomKari]

Don't be discouraged!

If you were able to get as far as you did with VB and Excel, you're on the right track for programming.

And the great thing is, if you're wanting to process and analyze data, there's nothing better than SAS for that.

So your journey is going in the right direction, and we're all here to help.

Tom

[Reeza]

@VicBrookes You'll find that if you put in any effort and it shows, the forum is more than helpful. Especially if you mock up data and show what you want. You'll get several different versions of code that will solve your issues as long as you ask your question clearly 🙂