How to use parameters (like macro variables) in the path of SASEguide's imported task files

[jimbarbour · Accepted solution]

Here's something of an example of controlling file names for upload/download:

Step 1:  I have a macro that generates one to many files based on the data available at the time.  I'm using data from within a SAS data set to name my output files, but you could use date macros.  I'm downloading, but this technique can also be used for uploading.

%MACRO  Create_Files_By_Package (Package_ID= );
	PROC  EXPORT  
		DATA=WORK.Fltrd_Assn_Mtrs_5DayIRR_LE30
		(WHERE=(Package_ID  =  "&Package_ID"))
		OUTFILE="/sasapps/soc/Package_Assigned_Meters/Mtrs_5DayIRR_LE30_&Package_ID..csv"  
		DBMS=CSV
		REPLACE
		;
	RUN;

	%Check_SYSERR;

%MEND;


/**  For each unique Package_ID (as identified by FIRST.), call a macro to       				**/
/**  create a CSV file from the SAS dataset for just that Package_ID             				**/
DATA  _NULL_;
SET   WORK.Fltrd_Assn_Mtrs_5DayIRR_LE30;
  BY  Package_ID;

	IF  FIRST.Package_ID  THEN
		CALL  EXECUTE ('%Create_Files_By_Package(Package_ID='||strip(Package_ID)||');');
		
RUN;

Step 2:  I set some macro variables to control the download and then execute the Copy Files Task in my Enterprise Guide workflow. 

%LET  Datafile=Mtrs_5DayIRR*;
%LET  Download_To=C:\Users\barbourj\Documents\Tech\SAS\Output;
%LET  Download_From=/sasapps/soc/Package_Assigned_Meters/&Datafile..csv;

 Note that here I'm using a wildcard so that all files matching Mtrs_5DayIRR* will be downloaded.  For example, files 

Mtrs_5DayIRR_LE30_WS2015_11.csv

Mtrs_5DayIRR_LE30_WS2015_12.csv

Mtrs_5DayIRR_LE30_WS2016_01.csv

Mtrs_5DayIRR_LE30_WS2016_02.csv

 would all be downloaded.

Everything in my example is driven by the data in a SAS data set, but you could set yours up to be by date, time, etc.

Jim

[PascalMaurice]

Hi Jim

thank you, I think your proposal could be the solution, until we find macro variables support in the SASEguide Import data task. I've opened another track to work on a translation of the Copy Files Task from Chris for my French Customers. And start to build a demo to show them they can use this Custom Task to answer their need.

thanks again Jim

[jimbarbour]

You are welcome, and good luck.

One thing that might be tricky.  You will notice that I'm passing a normal SAS variable in my CALL EXECUTE statement.  If you pass in a macro variable, there are conditions where it may not work properly due to the timing of the way the macro facility works in SAS.  This is particularly true if there are macro variables inside your macro logic that are derived from the passed macro variable.  In that case instead of: 

DATA  _NULL_;
SET   WORK.Fltrd_Assn_Mtrs_5DayIRR_LE30;
  BY  Package_ID;

	IF  FIRST.Package_ID  THEN
		CALL  EXECUTE ('%Create_Files_By_Package(Package_ID='||strip(Package_ID)||');');
		
RUN;

You might want to try the following:

DATA  _NULL_;
SET   WORK.Fltrd_Assn_Mtrs_5DayIRR_LE30;
  BY  Package_ID;

	IF  FIRST.Package_ID  THEN
		CALL  EXECUTE (%NRSTR('%Create_Files_By_Package(Package_ID='||strip(&Working_Directory&Run_Date)||');'));
		
RUN;

I am not at work right now, so I don't have an example in front of me, but I think I have the syntax of the command right.  The %NRSTR tells the macro facility not to resolve the passed macro variables and immediately begin execution.  Instead, the variables are resolved, then any macro variables depending on the passed macro variables are resolved, and then execution begins.

Again, good luck,

Jim

P.S.  If your data step that calls the macro is a long running step, you might want to use DOSUBL instead of CALL EXECUTE.   The difference is that when using CALL EXECUTE, your code will not be executed until after your data step finishes whereas with DOSUBL, your code should begin immediate execution.  I have not used DOSUBL a great deal but from reading Chris Hemedinger's blog, it appears to hold great promise.  If you have a long running data step driving your CALL EXECUTE statements, you might want to experiment with DOSUBL.

[jimbarbour]

PascalMaurice:

I'm at work now.  Here (below) is a cut and paste from a functioning program.  This may be helpful in terms of the syntax which can be a bit tricky.  The syntax I included above isn't quite right.  Better to use this actual working code as an example.  This code reads through a list of tabs (worksheets) exported as text from two different Excel workbooks, importing each tab in turn (163 of them for each Excel file) into SAS.  It compares each of the two sets tabs, a 100% sample vs. a 1% sample of data, looking for significant differences in the distribution of values and writes a report.

DATA _NULL_;
 SET WORK.Tab_Names;

 CALL EXECUTE ('%NRSTR(%Process_Excel_Tab(Tab_Name=' || strip(TabName) || ');)'); 

RUN;
%Check_SYSERR;

Inside the macro, I set up a number of parameters based on the name of the Tab that is passed into the macro.  These parameters then control a variety of programs that process data based on the parameters.  

%MACRO	Process_Excel_Tab(Tab_Name=);
%LET	DeBug			=	*;
%LET	Data_Type		=	100;
%LET	FileIn			=	&DataLib&Tab_Name._&Data_Type;
%LET	Data_Format		=	txt;
%LET	Report_Title	=	&Tab_Name._&Data_Type;
OPTIONS	SOURCE2;
%INCLUDE "&IncLib.Import_Program.sas";
OPTIONS	NOSOURCE2;
%*Check_SYSERR;

TITLE	"&Report_Title";
PROC	CONTENTS	DATA=Out.Percent_&Data_Type	POSITION;	
RUN		&Control_Value;	
%Check_SYSERR;

PROC	PRINT		DATA=Out.Percent_&Data_Type;			
RUN		&Control_Value;	
%Check_SYSERR;

/*--------------------------------------------------------------------------*/

%LET	DeBug			=	*;
%LET	Data_Type		=	1;
%LET	FileIn			=	&DataLib&Tab_Name._&Data_Type;
%LET	Data_Format		=	txt;
%LET	Report_Title	=	&Tab_Name._&Data_Type;
OPTIONS	SOURCE2;
%INCLUDE "&IncLib.Import_Program.sas";
OPTIONS	NOSOURCE2;
%*Check_SYSERR;

TITLE	"&Report_Title";
PROC	CONTENTS	DATA=Out.Percent_&Data_Type	POSITION;	
RUN		&Control_Value;	
%Check_SYSERR;

PROC	PRINT		DATA=Out.Percent_&Data_Type;			
RUN  &Control_Value;	
%Check_SYSERR;

/*--------------------------------------------------------------------------*/

%LET	DeBug			=	*;
%LET	Data_Type1		=	1;
%LET	Data_Type100	=	100;
%LET	HTMLout			=   &HTMLlib&Tab_Name._&Data_Type1._Percent_vs_&Data_Type100..html;
OPTIONS	SOURCE2;
%INCLUDE "&IncLib.Compare_Program.sas";
OPTIONS	NOSOURCE2;
%*Check_SYSERR;

/*--------------------------------------------------------------------------*/

TITLE  "&Tab_Name. -- &Data_Type1. Percent vs. &Data_Type100. Percent";
PROC	CONTENTS  	DATA=Out.Percent_&Data_Type100._Final  POSITION;
RUN		&Control_Value;	
%Check_SYSERR;

ODS  	HTML   	BODY="&HTMLout";
PROC	PRINT	DATA=Out.Percent_&Data_Type100._Final;
	ID	Range_Start  Range_End	;
RUN		&Control_Value;	
%Check_SYSERR;

ODS	 	HTML 	CLOSE;

PROC  DELETE  DATA=Out.Percent_&Data_Type1;
RUN   &Control_Value;	
%Check_SYSERR;

PROC  DELETE  DATA=Out.Percent_&Data_Type100;
RUN   &Control_Value;	
%Check_SYSERR;

PROC  DELETE  DATA=Out.Percent_&Data_Type100._Final;
RUN   &Control_Value;	
%Check_SYSERR;

%MEND	Process_Excel_Tab;

I wrote this code in a hurry in one night, so it's not the most elegant, but perhaps this may be helpful as something of an example how to work with macro variables while processing tabs exported as text from Excel.  I apologize if you already know all of this.  

Jim