Macro variable not resolving

[gsnidow]

Greetings all.  I am trying to use a FILENAME statement via pipe to get directory info for a specified folder.  There are two subfolders in my top folder, and they are returned to a dataset no problem.  My problem is that when I try to pass the folder\subfolder into a subsequent FILENAME statement using a macro variable, it does not seem to be resolving.  I am unable to paste into this editor, so I have attached what I have so far with comments.  Hopefully it will be clear enough.  I sure would appreciate any tips.  Thank you.

Greg

[hdodson_pacificmetrics_com]

Howdy,

     %LET will resolve one time, just before execution. So trying to assign a value from a dataset to a macro variable during execution, using %LET, will not work because SAS is already past that phase. You can however use SYMPUT to achieve the same goal.

     Looping through the folders array will only be helpful if you've accumulated all of the folders into the array. Your current code loops through the array after only the first observation is read in from "Folderlist". Also, the temporary array (though it is a great idea) will have a dimension of 1, because you're initializing it with one variable (SubFolder). I think the intention was to store all of the values of 'SubFolder' into the temporary array, which again is a good idea, but will not occur using the current form of your code.

      All that said, I think using the FILEVAR option on the INFILE statement is the best way to achieve your immediate goal. Below is how I'd implement it:

data FolderList2;                           

set FolderList;                           

length SubDir NewSubFolder $500;          

SubDir = "dir """||strip(SubFolder)||""" /B";

infile temp pipe filevar=SubDir end=last; 

do until (last=1);                        

  input;                                  

  NewSubFolder = strip(_infile_);         

  output;                                 

end;                                      

run;                         

Hope this helps,

Huey           

[gsnidow]

Huey, thank you for the reply.  I won't be able to try it until I get back to work on Monday, but I wanted to ask a question in the mean time.  I found this doc http://www2.sas.com/proceedings/sugi27/p082-27.pdf  and it only references ascii files.  The ultimate files I am trying to get to are .xlsx files.  Will your method work with Excel files?  From what you have posted, and what I have read, it seems like your suggestion is the way to go.  Thanks again.

Greg

[Patrick]

You will need to use Proc Import or define a libname with the Excel engine (needs Access to PC Files licensed).

Below some sample code using Proc Import. The code will only read the data from the first sheet in an Excel workbook.

You might need to tweak it for your data.

Chris Hemedinger wrote a great article on his blog. The title says "export to Excel" but it gives you actually also a lot of information about reading from Excel.   How do I export from SAS to Excel files: Let me count the ways - The SAS Dummy

/* list all .xlsx file in folders c:\_temp\<folder> */
filename dirlist pipe 'dir /B /S c:\_temp\*.xlsx';
data dirlist;
  length ThisFile $500.;
  infile dirlist;
  input;
  if countc(_infile_,'\')=3 then
  do;
    ThisFile=_infile_;
    output;
  end;
run;

%macro import(ExcelFile);
  proc import
    file="%bquote(&ExcelFile)"
      out=%sysfunc(substrn(%sysfunc(prxchange(s/\W+/_/oi,-1,%scan(&ExcelFile,-2,.\))),1,32))
      dbms=xlsx
      replace;
      getnames=no;
      guessingrows=2000;
      mixed=yes;
  run;
%mend;

data _null_;
  set dirlist;
  call execute( cats('%import(',ThisFile,')') );
run;

[gsnidow]

Thank you both so much for your help so far.  As of now, I have all of the complete paths of the files I need in a dataset called 'FList'.  I'm a little bit stuck on how I should proceed to pass each file path to an import macro.  I thought it would just be a matter of passing each file path to an array, then loop through the array using each element as the file in a proc import statement, but I really can't get my head around it.  Once again, I am unable to paste into the editor, so I have attached what I have so far.  I realize I could probably simplify it, but for now I would like to keep it broken into steps until I fully understand what it is doing.  Thanks again.

Greg

[Tom]

You can use the /s option on your DIR command to have Windows recurse through the subdirectories for you, so getting your FLIST file should be much easier.

%let topdir=c:\files ;

data flist ;

  infile "dir /b/s ""&topdir\*.xlsx"" " pipe lrecl=256 truncover ;

  input filename $256. ;

run;

You need to figure out how to import one sheet.  You can use the File->Import GUI and have it save the PROC IMPORT code that it generates. Or you could look into using a LIBNAME instead.

To generate code based on data I usually find it is easiest to use a data step instead of a complex macro. If you generate a macro to process one file then the code generated could just be a macro call for each source file.  Otherwise you could just generate the code without creating a macro.  If your sheets are compatible you can use PROC APPEND to generate a final merged datasset.

filename code temp;

data _null_;

  set flist ;

  file code;

  put 'proc import datafile=' filename :$quote. ' out=target replace; '

     / '  sheet = "Target" ; '

     / 'run;'

     / 'proc append base=final force; '

     / 'run;'

   ;

run;

%include code / source2 ;

[gsnidow]

Tom, thank you for the tip on the recursive dir command.  Indeed that would be easier, but I need to be able to filter out the folders and files I need, and I'm not sure how I would do that with your simpler method.  I'm stuck now in getting the proc import to work with a macro variable created by the file path stored in dataset flist.  My thought, and I need to keep it simple, is to set a macro variable with proc sql, then use that variable in proc import, but it is not working.  I have attached the file, and my problems start where I have put comments.  I sure would appreciate any tips, as at this point I've tried everything I understand how to do.  Thank you.

Greg

[Tom · Accepted solution]

The main issue is that you did not include the name of the file for PROC IMPORT to read in quotes.

You should have  DATAFILE= "&input_file"

If you are going to use PROC SQL to create the macro variable then you need to do something to remove the blanks from the end of the value.

You can add a SEPARATED BY clause

  INTO :input_file SEPARATED BY ' '

or just reassign the value to the macro variable and let the macro processor remove the trailing blanks.

%let input_file=&input_file;

The only filtering of filename that I see in your programs is:

1) Filenames that end in .xlsx.  This the DIR command can do and I included it in the example.

2) Looking for filenames that begin with 2012.  That is easy to do in either the input step or the step that is generating the code.

  if scan(filename,-1,'\') =: '2012' ;

[gsnidow]

Thank you so much Tom, I knew it had to be something minor.  As for the last update statement, I'm still getting errors, but I think I can trial and error it until it works.  Thank you for your good time.  Thank all of you for that matter.

Greg

[gsnidow]

DOH!  I forgot double quotes around the macro variable in the update statement.  It now works.  Next step, put it all in a loop to step through all observations in the dataset.

Greg

[Tom]

Still not sure what good the looping does. I find it much more logical to process the full list in one swell foop the way that PROC MEANS or other SAS processes do.

[gsnidow]

Tom, to your point, I may not need a loop.  However, I am trying to translate what I can do with VBA, where I create an array of all the files to process then loop through the array to process them all.  In the most simple terms, it would be something like this if I have an array called arFiles, with one element containing the file name, as my one dataset in SAS does

For i = 1 to ubound(arFiles) step 1

     process arFiles(i)

Next i

Now, in that, the 'process arFiles(i)' would take maybe 8 or 10 lines, but I hope you get the logic.  Perhaps this is not necessary in SAS, but my knowledge of how SAS processes is extremely limited, so I am constantly thinking in terms of how VBA would do something, and then I trial and error it until I can muddle a workable result.  So, in my dataset I have 65 filepaths to the Excel files I need to import.  My reasoning is that I use proc sql to get the first one that has not been processed, use proc import to import it, then move on to the next one until they are all done.  If you know of a good reference that would describe a better way to do it in one step I sure would appreciate it.  A lot of the posts I read here are just way beyond my comprehension of SAS, so I spend hours researching just the simple things like filevar and pipe.  Thank you.

Greg