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

I am trying to write an EG Custom Task to read into a SAS data set a range from an Excel worksheet which the user first selects and then assigns the columns to roles using the SAS Variable Selector.

I'm using ISASTaskDataAccessor to get information about the columns and everything seems to be working fine until I try to use the Consumer.ActiveData.Library and Consumer.ActiveData.Member in my SAS program - the log contains the following error: "ERROR: File WORK._EXCEL_.DATA does not exist".

I've looked at the code in Chris Hemedinger's book for the TopNReport example and also the code on GitHub for this and can't see any extra steps which might be needed to make this range available in WORK.

Does anyone have any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

Chris,

The Office Add-In will copy the data to the SAS session (in the manner you observed) just before your task is run, but not at the time that your UI is being shown.  If you need that to happen while your task is visible, then we'll need to look at the cues the Office Add-In needs in order to make that happen.

You could take a look at the Data Set to DATA Step task, which emulates some of what the Office Add-in is doing to copy the data.  The source for that is here.

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

View solution in original post

3 REPLIES 3
ChrisBrooks
Ammonite | Level 13

I think I'm slightly closer to the root of the problem now. In the Office add-in options I set the option to show the log after the task has run as well as showing a log of my own via a message box. It appears that what is happening is that my code is running (I'm using the SubmitSasProgramandWait method) and then afterwards the data is being imported into the work library via a data step using infile cards - and is then immediately deleted!

Placing a similar data step in my code doesn't work so somehow I need to get the Excel data into my work library before my code runs but as I said previously I can't find any reference as to how this done........

ChrisHemedinger
Community Manager

Chris,

The Office Add-In will copy the data to the SAS session (in the manner you observed) just before your task is run, but not at the time that your UI is being shown.  If you need that to happen while your task is visible, then we'll need to look at the cues the Office Add-In needs in order to make that happen.

You could take a look at the Data Set to DATA Step task, which emulates some of what the Office Add-in is doing to copy the data.  The source for that is here.

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
ChrisBrooks
Ammonite | Level 13

Chris

Many thanks - now you say that it all makes perfect sense! I don't actually need the UI to be shown during execution (in fact it's probably better if it isn't) so I've rejigged my code so it now definitely runs after the data is copied to work and everything works perfectly now.

Your suggestion about looking at the Data Set to DATA Step task is also helpful anyway as I'll probably be writing another Custom Task soon where I will want the UI to remain open.

Chris

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
  • 3 replies
  • 1144 views
  • 0 likes
  • 2 in conversation