Automatically compiling a set of sas programs from a specific location, in the most recent folder

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Automatically compiling a set of sas programs from a specific location, in the most recent folder

Hello,

 

Does anyone have any idea how to write a sas code that would access a folder (most recent create date) from a set location, in which multiple sas programs are saved and then run them automatically?

 

For a visualization of my problem:

 

Location: Libraries\Documents

Folders: 17102016     18102016

Folder 18102016 is the most recent and has 10 sas scoring programs: sasp1, sasp2, ...sasp10

 

I want a code that runs on 18102016 and compiles all 10 sas programs.

 

Thanks

 

 


Accepted Solutions
Solution
‎10-19-2016 05:01 AM
Contributor
Posts: 37

Re: Automatically compiling a set of sas programs from a specific location, in the most recent folde

[ Edited ]

In the end, I've realized I didn't need to run all sas programs in the most recent folder, in a specific location.

I had to scan them however for keywords and extract some numbers out of them.

The process of finding out a list of sas programs in the most recent folder, at a specified location was still a valid requirement.

I've developed the following code (shout out to @Astounding for giving me the idea of using YYYYMMDD format for the folders that contained sas files):

/* 	Read all the files in the specified location by invoking the command window */
FILENAME LocFiles PIPE ' dir "C:\Users\SUKLCO\Documents\L Projects\ICL\Simulations" /b /s ';

/*  Keep only those files that respect the naming convention: YYYYMMDD */
DATA LocFiles;                                     
	INFILE LocFiles TRUNCOVER;
	INPUT LocFiles $100.;
	LocFiles = INPUT( SCAN( LocFiles,-1,"\" ), 8. );
	IF LocFiles;
RUN;

/* 	Record the name of the most recent file (by looking at the date embedded in the name) in a macrovariable */
DATA LocFiles;
	SET LocFiles END=eof;
	RETAIN RecFile;
	IF LocFiles GT RecFile THEN RecFile=LocFiles;
	IF eof THEN CALL SYMPUT('RecFile', RecFile);
	DROP RecFile;
RUN;
%PUT &RecFile;

/*	Eliminate blanks in the macro variable's name */
%LET RecFile=&RecFile;

/*	Read all the sas code programs that belong to the latest VS Exploration by involing the command window */
FILENAME SASFiles PIPE " dir ""C:\Users\SUKLCO\Documents\L Projects\ICL\Simulations\&RecFile"" ";

/*	System data is provided along with the sas files, hence some character processing is needed */
DATA SASFiles;                                     
	INFILE SASFiles TRUNCOVER;
	INPUT SASFiles $100.;
	IF INDEX(SASFiles, '.sas')=0 THEN DELETE;
	SASFiles = SCAN( SUBSTR( SASFiles,1,INDEX( SASFiles,'.sas' )+3 ), -1, " " );
	KEEP SASFiles;
RUN; 

/*	Create incremented macro variables that record the names of the sas files: one per each */
DATA Estimates;
	SET SASFiles END=eof;
	CALL SYMPUT( "saspgm"!!STRIP( PUT(_N_,BEST.) ), SASFiles );
	IF eof THEN CALL SYMPUT('N',_N_);
RUN;
%PUT &saspgm1;
%PUT &saspgm2;

/*	Scan each sas program for keywords to import filename, compound, intercept and slope and add it to the existing data */
%MACRO ExtractEstimates;
	DATA Estimates;
		SET Estimates;
		%DO i=1 %TO &N.;
			IF SASFiles = "&&saspgm&i" THEN DO;
	   			INFILE "C:\Users\SUKLCO\Documents\L Projects\ICL\Simulations\&RecFile\&&saspgm&i" DELIMITER=")" ;
	   			LENGTH Filename $20. Compound $20.;
				INPUT @ "'Batch'n = " Filename @@;
	   			INPUT @ "'Compound'n = " Compound;
				INPUT @ "_beta_1_{2} _temporary_ (" Intercept / Slope;
			END;
		%END;
		Filename = COMPRESS( Filename,"'" );
		Compound = COMPRESS( Compound,"'" );
	RUN;
%MEND;
%ExtractEstimates

If someone would indeed want to run the sas programs instead of scanning them for keywords, an %INCLUDE statement inside my last macro would do the trick.

Thank you all for your ideas. They really helped with the approach I took.

View solution in original post


All Replies
Respected Advisor
Posts: 4,973

Re: Automatically compiling a set of sas programs from a specific location, in the most recent folde

Here are just a few ideas that may prove relevant:

 

  • Are the names of the programs the same every time?
  • Does the order in which they run matter?
  • Do you have control over the folder naming convention?  It's easier to locate the latest one using YYYYMMDD instead of DDMMYYYY as the naming scheme.
Contributor
Posts: 37

Re: Automatically compiling a set of sas programs from a specific location, in the most recent folde

1. The sas programs come from VA export score code, so they take the name of the Visualization they're in. They must reflect the information that's on the graph, hence the names will definitely variate
2. The order of the sas programs doesn't matter as the score codes are addressing data with different, mutually exclusive, filters
3. Yes, folder naming convention can be controlled
Esteemed Advisor
Posts: 5,198

Re: Automatically compiling a set of sas programs from a specific location, in the most recent folde

You may want to explore the external file functions that could prepare execution performed by %include.
Data never sleeps
Contributor
Posts: 37

Re: Automatically compiling a set of sas programs from a specific location, in the most recent folde

A VA exploration will be created for a batch of data. One exploration will contain several linear regressions that are adjusted by the user using the filter tab. Once the linear regressions are adjusted, their respective score codes are exported into a location of our choice, in a specified folder (naming convention can be controlled). My approach is to identify the latest folder from that location (either by using folder names or Windows created date) and run all score codes using the %INCLUDE statement on the same dataset.
Grand Advisor
Posts: 9,576

Re: Automatically compiling a set of sas programs from a specific location, in the most recent folde

You can use PIPE to the most recent SAS file.

filename x pipe 'dir c:\lib\doc\*.sas /s /b';
data x;
 .......get the recent sas file.........

 call execute();
run;
Grand Advisor
Posts: 9,576

Re: Automatically compiling a set of sas programs from a specific location, in the most recent folde

[ Edited ]


options noxwait;
filename x pipe 'dir c:\temp\*.sas /s /c';
data want;
 infile x ;
 input;
 length dir file $ 200;
 retain dir;
 if lowcase(left(_infile_)) =: 'c:\temp' then dir=scan(_infile_,1,' ');
 pid=prxparse('/^\d{4}\/\d\d\/\d\d/');
 if prxmatch(pid,_infile_) then do;
   file=_infile_; 
   datetime=input(catx(':',scan(_infile_,1,' '),scan(_infile_,2,' '),'01') ,anydtdtm32.);
   output;
 end;
format datetime datetime.;
drop pid;
run;
proc sql;
select distinct cats(dir,'\*.sas') into : dir
 from want
  having datetime=max(datetime);
quit;
options symbolgen;
filename xx "&dir.";
%include xx/source2;


Contributor
Posts: 37

Re: Automatically compiling a set of sas programs from a specific location, in the most recent folde

Hi,

Thanks for all the suggestions. I've managed to get my hands on 2 programs that do the trick except they need some adjusting accessing the most recent folder.
Solution
‎10-19-2016 05:01 AM
Contributor
Posts: 37

Re: Automatically compiling a set of sas programs from a specific location, in the most recent folde

[ Edited ]

In the end, I've realized I didn't need to run all sas programs in the most recent folder, in a specific location.

I had to scan them however for keywords and extract some numbers out of them.

The process of finding out a list of sas programs in the most recent folder, at a specified location was still a valid requirement.

I've developed the following code (shout out to @Astounding for giving me the idea of using YYYYMMDD format for the folders that contained sas files):

/* 	Read all the files in the specified location by invoking the command window */
FILENAME LocFiles PIPE ' dir "C:\Users\SUKLCO\Documents\L Projects\ICL\Simulations" /b /s ';

/*  Keep only those files that respect the naming convention: YYYYMMDD */
DATA LocFiles;                                     
	INFILE LocFiles TRUNCOVER;
	INPUT LocFiles $100.;
	LocFiles = INPUT( SCAN( LocFiles,-1,"\" ), 8. );
	IF LocFiles;
RUN;

/* 	Record the name of the most recent file (by looking at the date embedded in the name) in a macrovariable */
DATA LocFiles;
	SET LocFiles END=eof;
	RETAIN RecFile;
	IF LocFiles GT RecFile THEN RecFile=LocFiles;
	IF eof THEN CALL SYMPUT('RecFile', RecFile);
	DROP RecFile;
RUN;
%PUT &RecFile;

/*	Eliminate blanks in the macro variable's name */
%LET RecFile=&RecFile;

/*	Read all the sas code programs that belong to the latest VS Exploration by involing the command window */
FILENAME SASFiles PIPE " dir ""C:\Users\SUKLCO\Documents\L Projects\ICL\Simulations\&RecFile"" ";

/*	System data is provided along with the sas files, hence some character processing is needed */
DATA SASFiles;                                     
	INFILE SASFiles TRUNCOVER;
	INPUT SASFiles $100.;
	IF INDEX(SASFiles, '.sas')=0 THEN DELETE;
	SASFiles = SCAN( SUBSTR( SASFiles,1,INDEX( SASFiles,'.sas' )+3 ), -1, " " );
	KEEP SASFiles;
RUN; 

/*	Create incremented macro variables that record the names of the sas files: one per each */
DATA Estimates;
	SET SASFiles END=eof;
	CALL SYMPUT( "saspgm"!!STRIP( PUT(_N_,BEST.) ), SASFiles );
	IF eof THEN CALL SYMPUT('N',_N_);
RUN;
%PUT &saspgm1;
%PUT &saspgm2;

/*	Scan each sas program for keywords to import filename, compound, intercept and slope and add it to the existing data */
%MACRO ExtractEstimates;
	DATA Estimates;
		SET Estimates;
		%DO i=1 %TO &N.;
			IF SASFiles = "&&saspgm&i" THEN DO;
	   			INFILE "C:\Users\SUKLCO\Documents\L Projects\ICL\Simulations\&RecFile\&&saspgm&i" DELIMITER=")" ;
	   			LENGTH Filename $20. Compound $20.;
				INPUT @ "'Batch'n = " Filename @@;
	   			INPUT @ "'Compound'n = " Compound;
				INPUT @ "_beta_1_{2} _temporary_ (" Intercept / Slope;
			END;
		%END;
		Filename = COMPRESS( Filename,"'" );
		Compound = COMPRESS( Compound,"'" );
	RUN;
%MEND;
%ExtractEstimates

If someone would indeed want to run the sas programs instead of scanning them for keywords, an %INCLUDE statement inside my last macro would do the trick.

Thank you all for your ideas. They really helped with the approach I took.

Respected Advisor
Posts: 3,831

Re: Automatically compiling a set of sas programs from a specific location, in the most recent folde

@Elle

Isn't what you've posted here as the solution only half of the answer to the problem you've given us?

 

I can't see how you determine the latest folder and I also can't see how you actually execute the programs.

 

As for a folder naming convention: I suggest that you're using a YYYYMMMDD pattern because this will then also sort nicely if looking at it in Windows Explorer.

Contributor
Posts: 37

Re: Automatically compiling a set of sas programs from a specific location, in the most recent folde

[ Edited ]

Hello Patrick,

 

No, this code access the folders in a location.

I've taken the suggestion from @Astounding and named my folders in the format YYYYMMDD.

I've then scanned the location to get a list of all the folders and files (I'm only interested in the folders however - so I used an INPUT function to select only files expressed in numbers - folder name convention).

I've saved the most recent folder (using a call symput combined with retain to find the column maximum ) in a macro variable.

 

I then execute the code again only that now my location has been changed, as the last folder has been added to my previous location.

I access the sas program files, save every one of them in a macro var and use an %INCLUDE statement to execute them individually.

 

I've only finished the first part so far, so here you go, the names of all the files in my location:

/* 	Invoke MSDOS to read all files in a specific location */
%LET dirloc='dir "C:\Users\...\Simulations" /b /s';
FILENAME filelist PIPE &dirloc;

/*  The recorded files are in a format that specifies their location.
	I extract just the file name using the SCAN function and because the names are character,
	I convert them to numeric using the INPUT function. The idea behind this is that file names
	will be saved under the format YYYYMMDD 
*/
DATA Folders;                                     
	INFILE filelist TRUNCOVER;
	INPUT filelist $100.;
	filenames=INPUT( SCAN( filelist, -1, "\" ), 8. );
RUN;

DATA Folders;
	SET Folders END=eof;
	RETAIN mostrecent;
	IF filenames GT mostrecent THEN mostrecent=filenames;
	IF eof THEN CALL SYMPUT('mostrecent', mostrecent);
	DROP mostrecent;
RUN;
%PUT &mostrecent;


 

 

SAS Result

 Capture.JPG

I will update the solution with the entire process once I've finished. The code I found is the answer to my problem, it just needs tweaking a bit.

 

 

Contributor
Posts: 37

Re: Automatically compiling a set of sas programs from a specific location, in the most recent folde

@Patrick
I've posted my entire solution as I've developed it. I hope it's more clear than the previous one I offered. I just had it in my head and that code was the key to the entire process. It was easy to figure out the rest.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 546 views
  • 4 likes
  • 5 in conversation