SAS: How to Look into folder/subfolder. Open and concatenate data files (if condition was satisfied)

[Karem]

Hi Guys,

Kind a new in dealing with files and folders in SAS. I'm hoping someone can help me.

Procedure:

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!

[Reeza]

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. 

[ManoharNath]

is it possible to check for any string within code and create a flag for that string if its have been in code. if yes can you help me how to incorporate within this code.

[Reeza]

You should probably start your own thread/post and include specific details. Depending on what you want, there's a variety of options to do what you want. And a few cases where it won't work (macro generated code).

[ballardw]

---

@ManoharNath wrote:

is it possible to check for any string within code and create a flag for that string if its have been in code. if yes can you help me how to incorporate within this code.

---

Other cases that may make a search for "string within code" not likely to succeed:

Use of Call Execute creating code values from variables in a data set

Using a data step to write additional code files

Concatenation of variables/text into values you want.

And if you mean "check for a string the values of data set variables" that's another bit entirely.