topic Re: Accessing SQL views in Excel through MS Office add-in in Microsoft Integration with SAS

Accessing SQL views in Excel through MS Office add-in

Agnar — Thu, 05 Mar 2015 09:11:22 GMT

Hi all,

I am trying to find out whether it is possible to access a view created in EG through the Excel add-in.

The view is in a registered library and I am able to access a similarly created table without problems. Currently, I get an error "Unable to open "SASApp:library.viewname". ERROR: The following columns were not found in the contributing tables: _TEMV001." Again, there is no error when I open the view in EG.

Any thoughts?

Thanks

Re: Accessing SQL views in Excel through MS Office add-in

LinusH — Tue, 10 Mar 2015 19:59:50 GMT

Is the view registered in meta data?

Is the libname pre-assigned? If yes, how?

When you say SQL view, you means SAS SQL views, right? On Base SAS tables?

And is the view stored in the same libname as the tables?

Are you using the same profile when logging into SAS metadata?

Re: Accessing SQL views in Excel through MS Office add-in

Agnar — Wed, 11 Mar 2015 09:29:15 GMT

Hi Linus,

Thank you for the reply. The first question is the tricky one and the answer to it will probably solve my problem. Sorry for being a bit of a newcomer to all this, but I am using the same profile when logging into SAS on every account.

I've created a very simple table in EG (new Data - two variables a (numeric) and b (character)) and keep it in a pre-assigned library (I assume it's pre-assigned, it is under SASApp:Libraries in Enterprise Guide when I open it. I.e. I haven't created a special library through a libname statement).

I have no problem with accessing that table through the Excel Add-in. Then I created a very simple view in SQL (proc sql; create view EGREPCD.view_test as select sum(a) where b="x";). I can access the view via EG without problem. Even having two instances of EG will allow me to update the values in the table in one and see the view change in the other. However, trying to access the view in Excel throws the error.

I guess the problem lies in the simple SQL? Even if EG understands it, taking it a step further (into the Excel Add-in) is too much. If that is the case, is there a way for me to register the view in meta data? I have DI Studio if that matters.

Thanks a lot,

Agnar

Re: Accessing SQL views in Excel through MS Office add-in

LinusH — Thu, 12 Mar 2015 15:45:11 GMT

Just chose "Register tables" in DI Studio or SMC. If your view is created you should be able to see it when choosing the appropriate libref.

Re: Accessing SQL views in Excel through MS Office add-in

jakarman — Thu, 12 Mar 2015 16:08:40 GMT

When the view is a SAS view. You created the in EGREPCD the pre-assigned library that the view is using must also be active in AMO.

With Eguide you can have pre-assigned libraries that are not assigned. Eguide will open them when you need those. Also when you are using a different appserver or different user or ...

You will not have all data-items resulting in a failure of the view. You can check those using AMO and code some SAS statements.