BookmarkSubscribeRSS Feed
franz
Fluorite | Level 6

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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.

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
  • 6 replies
  • 1385 views
  • 1 like
  • 3 in conversation