Export to excel on a local PC

[deleted_user]

I might have asked this question before....but, I currently have SAS running on three servers.... I can create and run a stored process that exports an Excel file. However, the Excel file is saved to my server. What I want to do is instead have my process export that file to a local computer.

My reason for wanting this is I don't think users should have to get to the server and then copy the file to their local machines.

If anyone has a suggestion, would you please share it with me?

[Cynthia_sas]

Hi:
If your users have the SAS Add-in for Microsoft Office installed, then you no longer need to actually do an EXPORT to Excel. They can run your stored process from INSIDE Excel on their LOCAL machine and then they can do a FILE-->SAVE AS to save their results to their LOCAL machine.

If they run the same stored process from inside Microsoft Word, then the results could come back to them in a Word format and they could save the results as a Word DOC. And, if they run the same stored process from inside PowerPoint (assuming the results were a table the was fairly manageable to fit on one slide), then they could save the results as a local PPT file.

Is there a reason that you don't want to have the users accessing the stored process with the SAS Add-in for Office? That is one of the big advantages of the Add-in, in my opinion, is that the pulling of the results into Excel is pretty seamless from a user standpoint.

cynthia

[deleted_user]

Hi,

But what about if someone deos not have the Add In installed? How would the export then be done? How do I find the address of the local PC? Also, would I need to have the SAS Access Sortware installed in order to perform an actual Excel export?

Thanks for your patience and helpfulness.

[deleted_user]

Cynthia is best equipped to tell you how powerful the AddIn is for SAS users. I have never used it, and have found the Access product, the ODS tagsets, DDE, HTML and other methods have been adequate for creating most Excel results. There are many conference papers on these subjects if you search support.sas.com.

A PC always has a name on a domain. Going to a command prompt and typing "set" will usually bring the workstation name up for you. The environment variable used to store the value may change from place to place but is often a numeric value that is probably the same as the asset number stuck to the machine in most corporate environments. The C drive usually has an administrative share you can access with \\W123456\C$ and you can then define any subdirectory under this root.

Note that I say "can" because I am not sure this fits in the concept of a stored process. If one assumes the SP will be used by multiple people and / or from multiple machines, then hardcoding the destination folder seems counter-productive. I have done it for testing applications where I want delivery solely to me, but the SP should be delivered somewhere more generic.

Most project teams and workgroups have shared network folders, and this seems to be the ideal location for delivery of this content. I would counsel you to explore this option. You might also run a small code node on your Enterprise Guide and check the result of the following statement:

%Put I am &SysUserId.;

I would expect your log will contain the statement "I am " followed by your network user id. If you have user folders stored on your network drive, then you can separate the output from each user of the SP by using the path:

\\server\share\exports\&SysUserId.

I have noticed that EG 4.2 imports from Excel by parsing the file even when the Access PCFF product is licensed. I don't know whether the export uses a similar process, and cannot test this because PCFF is licensed on my machine. I'm sure someone from SAS can advise on that.

Kind regards

David

[deleted_user]

dkvj,

Thanks for replying. However, we do not have user folders stored on our network drives, hence my questions about how to get data from SAS to a user's local drive.

And...I do agree with your comments about hard-coding anything...I'm quite against that.

[Cynthia_sas]

Hi:
I'm still wondering why you would NOT put the SAS Add-in on the desktop of folks who need to get stored process output in Excel.

At this point, I think that your issues are going to involve Tech Support. Generally speaking, the stored process server and workspace server do NOT have visibility of a single user's drive, except by UNC name or network name. Of course, THAT is not any information that is passed to the stored process server from the BI client applications. So although the client apps (such as EG or the Portal) do KNOW the metadata ID of the person who is submitting a stored process, that metadata ID does NOT carry personal PC information with it.

If you wrote a custom web application, using the HTTP protocol and the Stored Process Web Application, you could run your stored process and then send the streaming results back with a content-type header that would launch Excel on the local (requesting) machine. Again, installing the SAS Add-in for the end-users looks WAY quicker and more efficient to me than writing a custom SPWA app.

So, you did NOT say which client application your users were going to be "in" when they requested the stored process to run. If they don't have the SAS Add-in for Microsoft Office, then they must either be using EG or Web Report Studio or the Info Delivery Portal. I can't imagine that you're having them run stored processes that have been turned into jobs using DI Studio. Anyway, it sounds like you have some very particular requirements for your stored process. So, at this point, Tech Support is your best resource.

cynthia