Macro to Loop Code On All Files in a Folder, different titles, same file format

[mkimmi]

What I Would Like To Do:

Apply the same code to all the files in a folder but the files have different file names. Can't just loop import and merge them before applying code since I need to cut some stuff out of the data set and transpose it before I merge them with one another.

I've written  code that works in importing one file and doing what I like to it, but would like to write a macro that will automatically do this for all the files in a designated folder. (Each folder is a year and there are two decades worth of files to sift through). I've put the code that works on a singular file after "call execute ("

I bolded the parts that I am having the most trouble with and am unsure if they're written correctly.

What I Have:

%macro MultImp(dir=,out=,year);

%let rc=%str(%'dir %")&dir.%str(\%" %');

filename myfiles pipe %unquote(&rc);

data list;

length fname $256.;

infile myfiles truncover;

input myfiles $100.;

fname=quote(upcase(cats("&dir",'\',myfiles)));

out="&out";

drop myfiles;

call execute(" 

  proc import 

           DBMS=XLS REPLACE;

           SHEET='Sheet1';

           GETNAMES=YES;

           DATAROW=6;

            datafile= '||fname||' replace ;

            out = pp

  run;

  data have;

  set pp;

run;

proc sql noprint;

select nliteral(name) into :oldname trimmed 

from dictionary.columns

where libname='WORK' and memname='HAVE' and varnum=1

;

quit;

data want;

 set have (rename=(&oldname=STATE));

run;

data uu (Keep=State B  R);

set want;

if not State>=0 and B='' then B='Total';

if State='' and R='' or R<0 then delete;

if B='$1 under $25,000' then B='Lower';

if B='$25,000 under $50,000' then B='Lower Middle';

if B='$50,000 under $75,000' then B='Middle';

if B='$75,000 under $100,000' then B='Upper Middle';

if B='$100,000 under $200,000' then B='Upper';

if B='$200,000 or more' then B='Rich';

run;

proc sort data=uu;

by State B;

run;

proc transpose data=uu out=uu_transposed;

by State;

id B;

run;

data mm (Keep=State C1 C2 C3 Class Year);

set uu_transposed;

C1=Lower;

C2=Lower_Middle + Middle + Upper_Middle;

C3=Upper + Rich;

array values C1-C3;

largest = max(of values[*]);

index    = whichn(largest, of values[*]);

LENGTH Class $ 10;

Class = vname(values[index]);

if Class = 'C1' then Class='Lower';

if Class ='C2' then Class ='Middle';

if Class ='C3' then Class ='Upper';

Year=&year.;

Drop index largest;

Rename _Name_=Name;

Name='Total Number of Returns';

run;

proc append base=||out|| 

data=mm;

run;

");

run;

filename myfiles clear;

%mend;

%MultImp(dir='/folders/myfolders/sasuser.v94/zipcode2014/',merged2014, 2014);

Please let me know if this doesn't make sense, or if there is any advice for this! Thanks!

[Reeza]

You've designed this in a fashion that's almost impossible to test. I would not recommend the call execute in the way you've done here. Instead, wrap all that code (import, data, transpose, recoding etc) into a separate macro and then call the macro once from the CALL EXECUTE for each file. 

Test each part of your code separately first as well to make sure it does what you expect. 

Spoiler

---

@mkimmi wrote:

What I Would Like To Do:

Apply the same code to all the files in a folder but the files have different file names. Can't just loop import and merge them before applying code since I need to cut some stuff out of the data set and transpose it before I merge them with one another.

I've written  code that works in importing one file and doing what I like to it, but would like to write a macro that will automatically do this for all the files in a designated folder. (Each folder is a year and there are two decades worth of files to sift through). I've put the code that works on a singular file after "call execute ("

I bolded the parts that I am having the most trouble with and am unsure if they're written correctly.

What I Have:

%macro MultImp(dir=,out=,year);

%let rc=%str(%'dir %")&dir.%str(\%" %');

filename myfiles pipe %unquote(&rc);

data list;

length fname $256.;

infile myfiles truncover;

input myfiles $100.;

fname=quote(upcase(cats("&dir",'\',myfiles)));

out="&out";

drop myfiles;

call execute(" 

  proc import 

           DBMS=XLS REPLACE;

           SHEET='Sheet1';

           GETNAMES=YES;

           DATAROW=6;

            datafile= '||fname||' replace ;

            out = pp

  run;

  data have;

  set pp;

run;

proc sql noprint;

select nliteral(name) into :oldname trimmed 

from dictionary.columns

where libname='WORK' and memname='HAVE' and varnum=1

;

quit;

data want;

 set have (rename=(&oldname=STATE));

run;

data uu (Keep=State B  R);

set want;

if not State>=0 and B='' then B='Total';

if State='' and R='' or R<0 then delete;

if B='$1 under $25,000' then B='Lower';

if B='$25,000 under $50,000' then B='Lower Middle';

if B='$50,000 under $75,000' then B='Middle';

if B='$75,000 under $100,000' then B='Upper Middle';

if B='$100,000 under $200,000' then B='Upper';

if B='$200,000 or more' then B='Rich';

run;

proc sort data=uu;

by State B;

run;

proc transpose data=uu out=uu_transposed;

by State;

id B;

run;

data mm (Keep=State C1 C2 C3 Class Year);

set uu_transposed;

C1=Lower;

C2=Lower_Middle + Middle + Upper_Middle;

C3=Upper + Rich;

array values C1-C3;

largest = max(of values[*]);

index    = whichn(largest, of values[*]);

LENGTH Class $ 10;

Class = vname(values[index]);

if Class = 'C1' then Class='Lower';

if Class ='C2' then Class ='Middle';

if Class ='C3' then Class ='Upper';

Year=&year.;

Drop index largest;

Rename _Name_=Name;

Name='Total Number of Returns';

run;

proc append base=||out|| 

data=mm;

run;

");

run;

filename myfiles clear;

%mend;

%MultImp(dir='/folders/myfolders/sasuser.v94/zipcode2014/',merged2014, 2014);

Please let me know if this doesn't make sense, or if there is any advice for this! Thanks!

---

[mkimmi]

Understood! I will definitely do that

My question is primarily concerned with the bolded part: all the stuff before call execute. I'm unsure how I could design the macro to open a folder (which is what I hoped to set "dir=" to), and then apply said code (after call execute--doesn't matter what the code is) to all the files in that folder regardless of their names?

[mkimmi]

@Reeza -- I saw you link https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type in another thread--gonna try it out! looks like something that I'm looking for

[Tom]

Is the question how to find the names of all of the files in a directory?

It looks like you are trying to run an operating system command to do this.  In that case you need to know what operating system your SAS session is running under and what command you can use to list the files.

If you SAS session is running under Windows then the DIR command with the /B option is the simplest.

%let dir=x:\some directory\;
data list;
  infile %sysfunc(quote(dir /b "&dir")) pipe truncover;
  input fname $256. ;
  fname=cats("&dir",fname);
run;

Note it is easier if you just add the \ to the end of the macro variable with the directory name. 

[mkimmi]

I was trying to import all files in a directory, and for each file, apply a code that'll change into a new dataset

So far, based on the code here: https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type, I understand how make a list of all the file names, then make a separate macro to proc import each of them. Then at the end, write a function to call the separate macro for each entry on the list.

What I'm trying to do now is to add to that separate macro my code, but I'm a bit stumped with the "catt('test', put(_n_, z2.)), ');');" part of the last step copied below:
%*Call macro once for each entry in the list table created from the %list_files() macro;
data _null_;
set list;
string = catt('%import_file(', dir, ', ', name,', ', catt('test', put(_n_, z2.)), ');');
call execute (string);
run;

[SASKiwi]

Add a PUT statement to write STRING to the SAS log to see if it generates correct macro calls:

%*Call macro once for each entry in the list table created from the %list_files() macro;
data _null_;
set list;
string = catt('%import_file(', dir, ', ', name,', ', catt('test', put(_n_, z2.)), ');');
put string = ;
* call execute (string);
run;

[Reeza]

I recommend using this approach : Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage - includes an example of getting a file listing
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

Another example you may find helpful:

How to add data to a regular report and then report on it daily

https://gist.github.com/statgeek/353374a5d8ea4f0c89ce5d80a47f4a4c

[mkimmi]

Hi yes thank you--I actually ended up creating my code based on this GitHub file you posted on another thread! so thank you!

Just more minor problems come as I continue forth