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

Reply
Occasional Contributor
Posts: 8

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

Hi Guys,

 

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

 

input_table.PNG

 

Procedure:

folder_file_structure.PNG

 

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.

 

 

xls_structure.PNG

 

measure_csv.PNG

 

output_table.PNG

 

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!

Super User
Posts: 19,167

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

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. 

Ask a Question
Discussion stats
  • 1 reply
  • 144 views
  • 0 likes
  • 2 in conversation