How to execute SAS codes in Excel

Reply
Contributor
Posts: 39

How to execute SAS codes in Excel

Hi,

Greetings,

Could you please let me know how do i execute sas codes from Excel, if i do how do i revert outputs to excel again???

SAS was not installed in my local system, we connect to PC SAS by using citrix, and SAS EG is not available in citrix too.

Thank you.


Super User
Super User
Posts: 7,407

Re: How to execute SAS codes in Excel

Sorry, please clarify.  SAS does not execute from Excel.  You could in theory generate a text file from Excel, then submit it via the SAS install, though my question would be why.  Write SAS programs within your SAS install.  Excel is useless at almost everything.

If you have some data in Excel, then in SAS import it via the many options - proc import, datastep, etc.  If you require Excel file outputs then do the programming in SAS, then proc report your data out via ODS.

Contributor
Posts: 39

Re: How to execute SAS codes in Excel

Sorry for Not having clear explanation..!! Here it goes..

Lets say i have an excel file, and sas code inorder to import and play with this excel. So we generally tend to import Data to SAS and execute piece of code. But i heard like, we can execute SAS codes in Excel it self, So

Excel will connect to SAS engine either PC SAS or SAS EG, and execute the code. If it is SAS EG that code executes in SASAPP - workspace server, that's what i heard, but iam not exactly sure.

So i need to have code(may be connection strings to PC SAS) to execute SAS Codes in Excel it self, saving the logs and reverting required outputs to excel with applied formats, so that i can directly . I believe this we can do by using tagsets.excelxp.

no need to open SAS externally, of course at the back end same story repeats.

Hope this makes you understand..!!

Super User
Super User
Posts: 7,407

Re: How to execute SAS codes in Excel

Sorry, why?  That sounds like madness to me.  Excel is not a programming IDE, and has none of the functionality therein (much like its not a database, or a data transport file, or an entry screen, or any of the other misuse its put through).  There are numerous problems with using Excel, "features" such as number/characters, hidden characters and formatting etc.  You also lose all the IDE functionality - code highlighting, interactive running, libraries, how are you going to debug things etc.

Super User
Posts: 17,864

Re: How to execute SAS codes in Excel

If you don't have the add-in on your local machine you're out of luck, you have to access it through citrix. I don't know why your company/institution has installed SAS via Citrix, perhaps take that up with the IT department. It doesn't sound very user friendly, though I'm sure it's easier to install/manage that way. 

Contributor
Posts: 39

Re: How to execute SAS codes in Excel

Yeah That's true. But i dont know why they have designed/built our system like this.

So is there any way to connect Excel and PC SAS.

Super User
Posts: 17,864

Re: How to execute SAS codes in Excel

Do you have the SAS Add In for Microsoft Office (AMO)?

SAS Add-In for Microsoft Office

If so you should see the SAS tab in Excel.  If you don't see it, the go to Excel options and you may have to re-activate the tab.  Sometimes Microsoft Security will disable the tab.

Contributor
Posts: 39

Re: How to execute SAS codes in Excel

Yes Reeza, Addin was installed Excel...

But this Addin wasnot installed in local computer's Excel. I need to call excel from Citrix and launch it from Citrix, Excel in Citrix has added addin of SAS. I am able to work on that Excel, and connecting to SAS engine also.

Super User
Posts: 6,946

Re: How to execute SAS codes in Excel

Have a good SAS interface (EG) installed in Citrix, or use SAS Studio (if you are alredy at SAS 9.4)

(What first came to mind as a response wasSmiley Happy

Take hammer. Put thumb on table. Hit thumb with hammer.

If pain is not sufficient, repeat with greater force.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 39

Re: How to execute SAS codes in Excel

Smiley Happy Funny,. Let me tell you why i need this.

I am going to create a tool(Kind of) for who are very new to SAS. As, every body knew Excel(My assumption) so that they can directly execute piece of code by either clicking button or by opening an excel.

My supervisor asked me to connect Excel and SAS to execute SAS codes, from Excel we need to pass the parameters required to SAS. Hope This makes sensce.

Thank you.

Super User
Posts: 6,946

Re: How to execute SAS codes in Excel

From the documentation, the MS Office Add-In is able to call a SAS stored process. So I suggest you experiment with that. Of course, you first need a tool with which to create a stored process, so you will need SAS Enterprise Guide in your Citrix environment.

I get the notion that your IT department is readying for a switch from Windows to Linux on the desktop, and therefore wants to move all Windows-only software to the Citrix server.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,407

Re: How to execute SAS codes in Excel

"I am going to create a tool(Kind of) for who are very new to SAS. As, every body knew Excel(My assumption) so that they can directly execute piece of code by either clicking button or by opening an excel."

So instead of having new users do training (e.g. from SAS, Certification), you plan to wrap the complicated part up in hidden code with a button the user can press.  How will they ever learn anything?  Its a good idea to have some training things, but there is no replacement for learning the tool.  What happens if they don't know Excel?  Wrap that up in something else?

Again, and IDE is specifically designed and written for the purposes of creating code within a project.  I can't comment on your IT situation though.

Valued Guide
Posts: 3,208

Re: How to execute SAS codes in Excel

Let me see analyse what is happening (politics).

Your IT department has problems with getting SAS software getting aligned to you normal desktop policies. They have decided delivering the SAS environment in an new segregated virtualized one would solve their IT problems. SAS support has agreed on that, both on them are happy and promoting it is success. Do not laugh as they are really believing that. SAS sold licenses getting their money and IT was busy burning budgets.

The only issue is, you have data that is accessible on your desktop not on that virtualized one. The integration for processing data was not a problem to solve by your IT and SAS.  

This is an big  example of the success on the IT business alignment factor, better to classify as the failure of it .

Now your boss is coming in asking for the integration of SAS and Excel.

If that is the situation, you have the following options.

- drop that citrix environment and deliver the SAS tools in your normal desktop environment

- implement all of you normal desktop environment into the Citrix environment. For cost saving dropping your normal desktop environment after that.

- rebuild the SAS tools like AMO and Eguide by your own.  The interfaces are open (.Net and Java) with Integration Technologies and metadata structures.

  You can drop the SAS licenses for these tools thereafter.

This is the high level vision for what you can do in a provocative way. By that is possible more clear in what kind of kafka situation you are in.

Delivering SAS to be used in AMO (MS office) is al there when you have Eguide for development and a metdataserver supporting the SP's 

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 12 replies
  • 491 views
  • 2 likes
  • 5 in conversation