02-29-2016 04:18 AM
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" ;
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 ?
03-02-2016 12:23 PM - edited 03-02-2016 12:25 PM
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
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.
02-29-2016 04:40 AM
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?
02-29-2016 07:29 AM
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:
02-29-2016 07:46 AM
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.
02-29-2016 07:51 AM
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 ?
02-29-2016 07:59 AM
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?
03-01-2016 01:26 AM
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...
03-01-2016 11:02 AM
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?
03-02-2016 01:40 AM
perhaps some idea to dig in, I'll work on it when some time to do it and tell you my thoughts
03-01-2016 01:39 AM
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.
03-02-2016 01:43 AM
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
03-02-2016 12:02 PM
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.
03-01-2016 11:12 AM
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:
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:
03-02-2016 01:50 AM
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.
Need further help from the community? Please ask a new question.