topic Re: Macro to include all .sas file from a folder in SAS Programming

Macro to include all .sas file from a folder

RAVI2000 — Tue, 24 Aug 2021 19:03:09 GMT

I am having multiple study folders which again have multiple folders for rawdata. In them, are my .sas files to input the rawdata. The .sas files have infile statement that includes \ (back slash) and while I am running them I get error since I am running them on unix environment. The macro should also convert the \ to / in the infile statement.

Project1

---->rawdata

---->dm

----> dm.csv (rawdata)

---->dm.sas (is including the dm.csv rawdata file and creating the formats in

---->ae

----> ae.csv (rawdata)

---->ae.sas (is including the ae.csv rawdata file and creating the formats in .sas file)

--->cm

--->cm.csv (rawdata)

--->cm.sas (is including the cm.csv rawdata and creating the formats in .sas file)

--->ex

similar

The sample dm.sas file to read the .csv file

data work.DEMOGRAPHICS frmts.DEMOGRAPHICS;
  %let _EFIERR_ = 0;
  infile '.\Demographics.txt' delimiter='09'x MISSOVER DSD lrecl=32767 firstobs=2 termstr=CRLF;
 INPUT SEQUENCE_NO  BIRTH_DATE AGE_ON_STUDY GENDER RACE ETHNICITY ELIGIBILITY_STATUS ON_STUDY_DATE ARMS;
if _ERROR_ then call symput('_EFIERR_',1);
run;

data DEMOGRAPHICS_FMT;
  set DEMOGRAPHICS;
run;
title "Demographics";

I have these .sas files for all .csv files for multiple projects.

Project2

---->rawdata

---->ae

----> ae.csv (rawdata)

---->ae.sas (is including the ae.csv rawdata file and creating the formats in .sas file)

--->cm

--->cm.csv (rawdata)

--->cm.sas (is including the cm.csv rawdata and creating the formats in .sas file)

--->ex

similar

I want to run all the .sas files in batch mode to get the raw datasets.

I have did the below to convert the \ to /, but for just one single .sas file. How do I do for all .sas file?

filename fixed '/Studies/Project1/rawdata/AE.sas';
data _null_;
  infile fixed;
  file fixed ;
  input;
  if left(upcase(_infile_))=:'INFILE' then _infile_=translate(_infile_,'/','\');
  put _infile_;
run;

How can I run all .sas file with multiple project folders?

Please let me know if you need any additional information.

PS: I am using SAS on UNIX, and all these projects are in a shared folder drive "z".

Re: Macro to include all .sas file from a folder

ballardw — Tue, 18 May 2021 21:24:33 GMT

Since programs tend to need to run in a specific order how do you control the order these %include files should execute?

If the code only creates formats and does not change then you should

1) create a permanent library to store the formats

2) make sure the formats are stored in that library : proc format libname=yourlib; for example

3) After that is done you only need one statement to make sure the formats are available by adding the library to the FMTSEARCH path. Something like: Options append=(fmtsearch(yourlib));

Re: Macro to include all .sas file from a folder

Reeza — Tue, 18 May 2021 21:28:19 GMT

1. Find all your .SAS files using a network directory call. This is asked and answered on here weekly so a search should find you results. Store them in a data set. I assume that your programs can be run in alphabetical order of the folders? If not, and a specific order is required, how is that determined?
2. Use CALL EXECUTE to %include each file

data _null_;
set list_files;
str = catt('%include "',
file_path,
'" / source;');
call execute(str);
run;

@RAVI2000 wrote:

I have a folder which again has multiple folders. In them, are my .sas file.

Project1

---->rawdata

---->ae

----> ae.txt (rawdata)

---->ae.sas (is including the ae.txt rawdata file and creating the formats in .sas file)

--->cm

--->cm.txt(rawdata)

--->cm.sas (is including the cm.txt rawdata and creating the formats in .sas file)

--->ex

similar

.

.

.

So, clearly it has different individual .sas files for formats in individual subfolders. I want a macro that can include all the .sas format files that I can use in the program. Please let me know if you need any additional information.

Re: Macro to include all .sas file from a folder

SASKiwi — Tue, 18 May 2021 22:31:08 GMT

One easy way to ensure your programs run in the correct order is to add a number to the start of your program names like so:

ae.sas ==> 010_ae.sas

cm.sas ==> 020_cm.sas

ex.sas ==> 030_ex.sas

When you sort by program name these will always be in run order.

Re: Macro to include all .sas file from a folder

Ksharp — Wed, 19 May 2021 12:25:09 GMT

Usine OS command to get all these sas code. and Call execute() to execute it .

filename x pipe 'dir c:\temp\*.sas /s /b';
data _null_;
infile x;
input ;
put _infile_;
/*call execute(catt('%include "',_infile_,'";'));*/
run;

Re: Macro to include all .sas file from a folder

RAVI2000 — Wed, 19 May 2021 14:57:18 GMT

I don't have a specific order for them. Is it necessary that they have to be in order?

Re: Macro to include all .sas file from a folder

Tom — Wed, 19 May 2021 14:59:14 GMT

@RAVI2000 wrote:
I don't have a specific order for them. Is it necessary that they have to be in order?

Depends on what they DO.

Re: Macro to include all .sas file from a folder

RAVI2000 — Wed, 19 May 2021 15:57:52 GMT

Hello Tom, can you explain me your code in detail. I am not catching it.
data filelist;
length dname filename $256 dir level 8 lastmod size 8;
format lastmod datetime20.;
input dname;
retain filename ' ' level 0 dir 1;
cards4;
/folders/myfolders
;;;;

data filelist;
modify filelist;
rc1=filename('tmp',catx('/',dname,filename));
rc2=dopen('tmp');
dir = not not rc2;
if not dir then do;
fid=fopen('tmp','i',0,'b');
lastmod=input(finfo(fid,foptname(fid,5)),NLDATM100.);
size=input(finfo(fid,foptname(fid,6)),32.);
fid=fclose(fid);
end;
else do;
dname=catx('/',dname,filename);
filename=' ';
lastmod=input(dinfo(rc2,doptname(rc2,5)),NLDATM100.);
end;
replace;
if dir;
level=level+1;
do i=1 to dnum(rc2);
filename=dread(rc2,i);
output;
end;
rc3=dclose(rc2);
run;

Re: Macro to include all .sas file from a folder

Tom — Wed, 19 May 2021 16:13:18 GMT

First step is to create a dataset that has the full structure for storing the directory contents information. It also has one observation for every top level path you want to traverse.

The second step use the MODIFY statement to allow you to process this dataset and add observations to it at the same time. As you output more observations they are added to the end and so will get re-processed by this same data step as new input records. This is the "secret sauce" that let's essentially do recursion.

The guts of the data step will

1) Create a fileref TMP pointing to the current file.

2) Try to open that fileref as if it was a directory. This is what detects any subdirectories (including the original top level nodes).

3) Set the DIR flag variable to true (1) when the file is a directory.

4) Based on whether or not it is a directory it uses different paths to gather information on the file.

5) It replaces the current observation in the dataset. (so it updates those other variables).

6) When it is a directory it adds new observations for all of the files in the directory. The new observations will then be processed by steps 1-5 in the later iterations of the data step.

For more details look up the functions being used.

Note: Removing the indentation like in your post makes it harder to read the code.

Re: Macro to include all .sas file from a folder

Tom — Wed, 19 May 2021 16:32:49 GMT

See this thread for more discussion and a more complete version of this code at the end from @ChrisNZ

https://communities.sas.com/t5/SAS-Programming/Size-of-a-directory/m-p/642202/highlight/true#M191554

Re: Macro to include all .sas file from a folder

RAVI2000 — Thu, 27 May 2021 19:20:09 GMT

Hello @Ksharp,
Thank you for your input!
I was trying your code my using my folder directory. It is not working. I am attaching my code for you reference. Please correct me where I am wrong.

filename x pipe 'dir Z:\Trials\ProjectA\RawData\DSMC_Datacutoff_20210316\StudyData-20210316\*.sas /s /b';
data _null_;
infile x;
input ;
put _infile_;
/*call execute(catt('%include "',_infile_,'";'));*/
run;

The StudyData-20210316 again has multiple folder like AE, LB, RESPONSE, etc., The .sas files are in each folder.

Re: Macro to include all .sas file from a folder

Reeza — Thu, 27 May 2021 19:24:54 GMT

Log.

Re: Macro to include all .sas file from a folder

Ksharp — Fri, 28 May 2021 11:52:45 GMT

Post your log.
And in log should display all these .sas filename .

P.S Assuming your PIPE could be execute under SAS.

Re: Macro to include all .sas file from a folder

RAVI2000 — Fri, 28 May 2021 17:16:21 GMT

23         filename x pipe 'dir
23       ! Z:\Trials\ProjectA\Data\DSMC_Datacutoff_20210316\StudyData-03-16-2021\*.sas /s /b'
23       ! ;
24         data _null_;
25         infile x;
26         input ;
27         put _infile_;
28         /*call execute(catt('%include "',_infile_,'";'));*/
29         run;

2                                                          The SAS System                                 12:12 Friday, May 28, 2021

NOTE: The infile X is:
      
      Pipe command="dir 
      Z:\Trials\ProjectA\Data\DSMC_Datacutoff_20210521\StudyData-03-16-2021\*.sas /s /b"

dir: cannot access Z\:TrialsProjectADataDSMC_Datacutoff_20210316StudyData-03-16-2021*.sas: No
 such file or directory
dir: cannot access /s: No such file or directory
dir: cannot access /b: No such file or directory
NOTE: 3 records were read from the infile X.
      The minimum record length was 48.
      The maximum record length was 155.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414
NOTE: The SAS System used:
      real time           0.03 seconds
      cpu time            0.02 seconds

Re: Macro to include all .sas file from a folder

Reeza — Fri, 28 May 2021 17:41:19 GMT

Are you using SAS EG? Is it running on a server with access to the Z drive? Or is the Z drive a local drive for you?

Re: Macro to include all .sas file from a folder

RAVI2000 — Fri, 28 May 2021 20:23:12 GMT

I don't have sas on my windows. I use cisco connect which takes me to a remote desktop. There I run on UNIX using terminal and giving UNIX comands.

Re: Macro to include all .sas file from a folder

Reeza — Fri, 28 May 2021 20:29:26 GMT

And the Z drive is where? That path looks like a Windows path not Unix path.

Re: Macro to include all .sas file from a folder

RAVI2000 — Tue, 01 Jun 2021 13:49:37 GMT

The folder is a shared folder which we have to map manually. Once I map it then only I am able to access it.

Re: Macro to include all .sas file from a folder

Kurt_Bremser — Tue, 01 Jun 2021 14:07:02 GMT

Z\:

is incorrect syntax. The colon has to immediately follow the drive letter:

Z:\

Re: Macro to include all .sas file from a folder

RAVI2000 — Tue, 01 Jun 2021 17:28:36 GMT

Thank you for the input Kurt! But if you observe my log, I have given it correctly. But SAS is reading it like this Z\:

How can I correct it?