Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

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

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


Accepted Solutions
Solution
‎03-03-2016 12:24 AM
Frequent Contributor
Posts: 104

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

[ Edited ]

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

 

View solution in original post


All Replies
Super User
Posts: 7,393

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

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 12

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

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:

 

 

Super User
Posts: 7,393

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

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 12

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

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

Super User
Posts: 7,393

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

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 1,290

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

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

Occasional Contributor
Posts: 12

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

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

PROC Star
Posts: 1,143

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

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

Occasional Contributor
Posts: 12

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

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

Frequent Contributor
Posts: 104

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

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

Occasional Contributor
Posts: 12

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

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

Frequent Contributor
Posts: 104

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

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

Community Manager
Posts: 2,884

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

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:

  1. Select File->Project Properties. The Properties window appears.
  2. Select the File References tab.
  3. Check the box: "Use paths relative to the project for programs and importable files"
Occasional Contributor
Posts: 12

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

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

 

☑ This topic is solved.

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

Discussion stats
  • 18 replies
  • 565 views
  • 3 likes
  • 6 in conversation