BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PascalMaurice
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
jimbarbour
Meteorite | Level 14

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

18 REPLIES 18
Kurt_Bremser
Super User

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?

PascalMaurice
Calcite | Level 5

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:

 

 

Kurt_Bremser
Super User

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.

 

PascalMaurice
Calcite | Level 5

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

Kurt_Bremser
Super User

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?

Quentin
Super User

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

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
PascalMaurice
Calcite | Level 5

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

TomKari
Onyx | Level 15

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

PascalMaurice
Calcite | Level 5

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

jimbarbour
Meteorite | Level 14

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

PascalMaurice
Calcite | Level 5

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

jimbarbour
Meteorite | Level 14

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

ChrisHemedinger
Community Manager

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"
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
PascalMaurice
Calcite | Level 5

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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