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:
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.
@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 importDBMS=XLS REPLACE;SHEET='Sheet1';GETNAMES=YES;DATAROW=6;datafile= '||fname||' replace ;out = pprun;data have;set pp;run;proc sql noprint;select nliteral(name) into :oldname trimmedfrom dictionary.columnswhere 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!
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?
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.
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.