Desktop productivity for business analysts and programmers

File import on a scheduled project

Reply
Occasional Contributor
Posts: 14

File import on a scheduled project

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

Super User
Super User
Posts: 9,200

Re: File import on a scheduled project

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.  

Occasional Contributor
Posts: 14

Re: File import on a scheduled project

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.

 

 

Super User
Posts: 9,560

Re: File import on a scheduled project

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 14

Re: File import on a scheduled project

Posted in reply to KurtBremser

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!

Super User
Posts: 9,560

Re: File import on a scheduled project

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 14

Re: File import on a scheduled project

Posted in reply to KurtBremser

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.

Ask a Question
Discussion stats
  • 6 replies
  • 125 views
  • 1 like
  • 3 in conversation