File import on a scheduled project

[franz]

Hi

Case scenario:

I create a simple SAS EG project with one flow. I am importing an excel file called "myfile_201710.xslx" and saving it into a dataset called "sasdata.myimporteddata_201710.xlsx". I then scedule my egp and all is fine. The following month I want to be able to run the same project, but using myfile_201711.xslx to create sasdata.myimporteddata_201711.xlsx.

I need to parametrize the date part of the file name and have the same project run every month without changing the project itself. In SAS Base I would simply create a macro with the date and use it as part of the name (ie myfile_&yyyymm..xslx), but in SAS EG I can't find a way to parametrize the input file name.

The excel file is on my windows local machine and the SAS App server is on a Win Server.

Thanks

[RW9]

Why not just rename the file something consistent and then you wouldn't have an issue?  Items which change in filenames are a real bugbear to me, use a version control system, or something like that and it really isn't a good idea.  

[franz]

Hi, 

Thanks for your reply, that would be a good workaround, but not a true solution. I am at a customer site and I have to deal with what I have.

[Kurt_Bremser]

Export your project code to a single .sas file.

Replace the relevant parts with macro variables, and set these with %sysget from environment variables.

Create a batch/script file that sets the environment variables (eg from the current date), and then calls your .sas with SAS.

Schedule the batch/script file as needed.

Once you have done this, your script will also be ready to run by a full enterprise scheduler. At least you have the blueprint for that.

PS All your data files should also be moved to the server. Keeping BI data on unreliable desktops is not recommended.

[franz]

Hi, thanks for your reply.

I do not want to export to sas as every change to project would mean redeploying the .sas.

Also, as ever, the devil is in the details... the infile statement in the generated code is something like this

"INFILE 'W:\EGWORK\_TD4884_SRV01_\#LN00007' ..... "

As you can see it does not reference the excel file by name directly. The generated code header comment also states

/*
   Note: In preparation for running the following code, the Import
   Data wizard has used internal routines to transfer the source data
   file from the local file system to SASApp. There is no SAS code
   available to represent this action.
*/

Which says exactly that the code will not run "as is" if not called from within EG.

I have a long experience with SAS in all flavours, so I could easily find workarounds to the problem, like the one you mention in your reply, but I'd like to do things exactly as stated in my the use case of my original question. I think this is not possible.

Thanks!

[Kurt_Bremser]

Since the Import task does not allow any access to the input filename, it is not possible.

Since EG can access server-side macro variables (for conditions), it would be a nice enhancement if one could use macro variables (and user prompt results, while we're at it) in such filenames.

You might consider to enter that in the SASWare Ballot.

[franz]

Hi, exactly... you've come to my same conclusion.

I have also tried to input a file name with a macro var reference, but EG will not allow it as the file would not exist.

Thanks for your time and answers.