05-15-2017 10:44 PM
Kind a new in dealing with files and folders in SAS. I'm hoping someone can help me.
1. Go to MainFolder
2. Look for subfolder (1st row: Sub1) declared into the Input_Table
3. Open EACH subfolder from Sub1_? to Sub1_1_?
4. Open *.xls file. see *.xls sample.
Locate cell C2
IF value of C2 is equal to dataID (1st row: abc123 of Input_Table)
5. THEN OPEN measure.csv. see measure.csv sample
6. repeat 2-5 (next row of Input_table)
7. concatenate measure.csv, see output table.
Im using SAS9.4m3 / Windows 7.
I hope i illustrate my query properly. Let me know if you have questions.
Appreciate if you can explain the codes as well.
thank you very much!
05-15-2017 10:56 PM
There's a macro in the macro appendix that will list all folders/subfolders etc for you.
This relatively long macro will do the same and store it in a dataset for you.
%macro list_files(dir,ext); %local filrf rc did memcnt name i; %let rc=%sysfunc(filename(filrf,&dir)); %let did=%sysfunc(dopen(&filrf)); %if &did eq 0 %then %do; %put Directory &dir cannot be open or does not exist; %return; %end; %do i = 1 %to %sysfunc(dnum(&did)); %let name=%qsysfunc(dread(&did,&i)); %if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then %do; %put &dir\&name; %let file_name = %qscan(&name,1,.); %put &file_name; data _tmp; length dir $512 name $100; dir=symget("dir"); name=symget("name"); path = catx('\',dir,name); the_name = substr(name,1,find(name,'.')-1); run; proc append base=list data=_tmp force; run; quit; proc sql; drop table _tmp; quit; %end; %else %if %qscan(&name,2,.) = %then %do; %list_files(&dir\&name,&ext) %end; %end; %let rc=%sysfunc(dclose(&did)); %let rc=%sysfunc(filename(filrf)); %mend list_files;
IMO this question is unreasonable to expect an answer to all at once. You're better off trying it yourself and asking for help with specific portions. You seem to have the process mapped out so you should be able to find examples online or here for each section.