Hi Scott
First, thanks for trying to debug all the rest of my code, but I must stress two points:
1. I am obviously not the best SAS macro coder, but learning. Thanks for teaching me, but bear with me also.
2. Remember the original objective of my question: I need to get a list of files, and then from each file name, pick up some clues which will tell me how to deal with that workbook.
Further from that, but that was not part of my question, I do various calculations on the data, transpose and append the data, etc.
Also, thanks to your Loop control macro, I have a solution that satisfies the objective of my posting.
I will, though, answer some of your questions:
* Do all the workbooks have similar structure?
No. See my original post. "The exact nature of the files (e.g., the worksheets contained) differs."
* Are the worksheet names valid SAS dataset names?
That is immaterial, since my code gives each worksheet, which is imported as a dataset of its own, a unique dataset name.
And even those names are arbitrary, because once transposed and appended, I don't need the original dataset name any more. Further to that, I do store information in variables which tells me where the data came from, so I can actually retrace it.
* Are the worksheet names repeated across workbooks? If so, how do you handle duplicate names, so datasets don't get overwritten?
Yes, the worksheet names are consistent. See my answer above for the rest.
* Otherwise, what output dataset naming pattern do you want?
See my answer above.
The arbitrary dataset names are created in my code, and then used in the source macro for the Loop control:
OutFile1=CATS("D_",_FileNo,"_",_Sheet);
OutFile2=CATS("D_",_FileNo,"_",_Sheet,"_Tr");
* Do all the worksheets have valid SAS column names in row 1?
Yes. It wasn't necessary to explain that, because it was not the reason for my post, and has no bearing at all on the question asked.
* If not, how do you intend to name the resultant SAS columns (likely named F1, F2, etc)
n/a
* Will your worksheet structure ever change? For example, will AU workbooks ever have more (or less) than 5 worksheets?
Strictly speaking yes, my worksheet structure will change, but the second part of this question refers to workbook structure, not worksheet structure. So I will answer both.
Workbook structure will be the same for all workbooks from the same region, but not across regions. So US and WW have two worksheets that I want, and several worksheets that I don't want. AU has those two, and three others that I want, together with a number of worksheets that I don't want.
Worksheet structure will change in that while Columns A and B will always be consistent, the exact number of columns in each worksheet (even worksheets of the same type) may vary. This is a result of the manner in which the original data were captured. Something that might help explain the code I posted, but was not needed for the objective of my posting (i.e., it was not something I asked about, because it was not something I needed help with) is that from Columns C onwards of each worksheet, data are contained across a pair of columns, with one cell being blank, and one cell containing a value, and which of those is the case is almost impossible to determine without physically examining the file. There is no way that I will be doing that for all those columns in all those worksheets in all those workbooks, which is why i have this code to take the samplings (values spread across two columns) and combine them into samples (one variable for every two columns):
%DO i=2 %TO (&NCols+1)/2;
%LET c=%EVAL(&i * 2 - 2);
%LET d=%EVAL(&i * 2 - 1);
IF MISSING(Sampling_&c) THEN Sampling_&c=0;
IF MISSING(Sampling_&d) THEN Sampling_&d=0;
Sample_&i=MAX(Sampling_&c,Sampling_&d);
%END;
But as I intimated before, all this is moot, because I really only had one question, which was, after creating a list of file names, how to capture the information I needed from the file name, and that has been answered.
Thank you
Click image to register for webinar
