topic Re: How to use parameters (like macro variables) in the path of SASEguide's imported task files in SAS Enterprise Guide

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

PascalMaurice — Mon, 29 Feb 2016 09:18:32 GMT

Hi there,

We have been using macro-variables to parameterize the paths of the imported files, for example:

%let year = 2015 ;

filename in "&rootIn./myWorkBood&year..xls" ;

proc import...

now is we want to replace the old timer code by a brand new SASEguide task to import the data from the Excel Workbook, I do not find any way to get a variable known as well as by SASEguide as my sas Code, like _CLIENTPROJECTPATH

any good idea ?

thank you

Pascal

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

Kurt_Bremser — Mon, 29 Feb 2016 09:40:17 GMT

In a project, create a process flow called Autoexec, and define basic (mostly static) macro variables in a code there.

Use user-defined prompts to supply the more dynamic macro variables.

How do you get the Excel workbook to the UNIX server?

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

PascalMaurice — Mon, 29 Feb 2016 12:29:53 GMT

Hi Kurt,

the question is associated with the SEGuide task, not sas code, and in the SEGuide task you cannot (or I do not find how to) use macro-variables in the path of the Excel Workbook to be imported:

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

Kurt_Bremser — Mon, 29 Feb 2016 12:46:56 GMT

That is because the read of the Excel file is not done in SAS, but in the Enterprise Guide. Since EG is not able to run/interpret SAS code on its own, it can't take the contents of macro variables (which only exist in the workspace server) into account.

When you import the Excel file, EG uses a Microsoft-supplied module to convert the Excel sheet into text data, transfers that to the workpsace server, and then runs import code to read that text file in the workspace server.

The filename of the Excel file can not be parameterized, AFAIK.

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

PascalMaurice — Mon, 29 Feb 2016 12:51:54 GMT

But sometimes there is some things passed from SEGuide to SAS Workspace, for example the SAS Workspace knows where the SASEguide project has been saved.

and ok, there is no SAS macro-variable that can be used in this task, is there another way to provide parameters to be used in the path of the files imported ?

thank you

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

Kurt_Bremser — Mon, 29 Feb 2016 12:59:03 GMT

How to automate the ETL process for Excel data depends very much on the topology of your SAS/BI setup.

Do you have a single machine, or a client-server? If client-server, what is the operating system of the server, and how is it interconnected with the rest of your IT landscape?

Where does Excel data reside? On clients, or on network drives?

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

Quentin — Mon, 29 Feb 2016 13:18:46 GMT

In addition to @Kurt_Bremser's questions, do you have SAS/ACCESS to PC Files?

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

PascalMaurice — Tue, 01 Mar 2016 06:26:54 GMT

Hi all,

I known all this stuff and alternatives to export SAS data to Excel, but my question is: how to use variables (SAS or not SAS...) to use parameters in the imported files path of the SASEguide task.

I do not find any solution on my side, that's why I've asked to the community, hoping somebody already worked and addressed this need...

If there no way, I'll post it in the SASWare Ballot...

thank you

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

jimbarbour — Tue, 01 Mar 2016 06:39:17 GMT

Pascal,

What I do is to:

1. Export my spreadsheet as Tab delimited text. I have a VBA macro to do this if you're interested.

2. I then import the data into SAS EG, but I mark the task as runnable outside SAS. I don't have EG open in front of me, but there's a check box that says something like that. You *must* check the box.

3. I then take the generated code and cut and paste it into a SAS program inside EG.

Sounds a little convoluted, but once you've exported the code, it just runs and runs, and needs no intervention if you've set your macros up correctly.

Jim

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

TomKari — Tue, 01 Mar 2016 16:02:27 GMT

I'm not an expert on custom tasks, but...

Would it be possible to run a small "invisible" SAS program from your custom task, that would get the values of the macro variables from SAS and then return them to your custom task for you to use?

Tom

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

ChrisHemedinger — Tue, 01 Mar 2016 16:12:33 GMT

If you can store your import file (Excel) in the same path as your project (or in a subfolder along side of it), then you can use Relative File paths in SAS Enterprise Guide to keep your project portable.

See "How to enable relative file references" section in this blog post about source control. Excerpted here:

How to enable relative file references

SAS Enterprise Guide allows you to link in SAS programs and external data files (such as Excel or CSV files), so you don't have to lock up all of your content in the project (EGP) file. When working with source control, you need to enable one additional trick: tell SAS Enterprise Guide to treat these file references as relative paths. (There's nothing like an absolute file path -- specific to your machine -- for messing up your collaboration effort.)

This setting is maintained per project. To set it:

Select File->Project Properties. The Properties window appears.
Select the File References tab.
Check the box: "Use paths relative to the project for programs and importable files"

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

PascalMaurice — Wed, 02 Mar 2016 06:40:27 GMT

Hi Tom

perhaps some idea to dig in, I'll work on it when some time to do it and tell you my thoughts

thank you

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

PascalMaurice — Wed, 02 Mar 2016 06:43:26 GMT

Hi Jim

there si some thing you cannot manage is the part of the importing task that extract the data from the Excel or csv file and then upload them on the server to be available to the sas data step.

You can then use a macro to build the full pathname of the transfered file, but not to build the filename of the Excel or csv file

thank you

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

PascalMaurice — Wed, 02 Mar 2016 06:50:16 GMT

Hi Chris,

nice to see you on my track !

your proposal is perhaps the beginning of a solution for the path of the file to be imported, now we have to find something to manage the file name itself: for example you have a myWorkbook_2015.xls file and you want to make something like myWorkbook_&year..xls because the name of your excel file change over the year.

thank you

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

jimbarbour — Wed, 02 Mar 2016 17:02:19 GMT

PascalMaurice,

Have you seen Chris Hemedinger's Copy Files Task? It's super useful. I use it to upload/download files all the times using macro variables inside my program(s). Let me see if I can find an example.

Jim

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

jimbarbour — Wed, 02 Mar 2016 17:25:48 GMT

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

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

PascalMaurice — Thu, 03 Mar 2016 05:24:34 GMT

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

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

jimbarbour — Thu, 03 Mar 2016 10:23:20 GMT

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.

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

jimbarbour — Thu, 03 Mar 2016 17:53:09 GMT

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