topic Re: Unable to see all the views/Tables while registering a library in SAS SMC. in SAS Enterprise Guide

Unable to see all the views/Tables while registering a library in SAS SMC.

Karthikk — Thu, 21 Apr 2016 12:22:54 GMT

Hi,

After following the steps of creating ODBC Server using dsn with sql server authentication and defining ODBC server and library in SAS SMC given authentication domain in server manager i am able to connect to the database(MS Sql Server).

I am now trying to register the views which are there in the database by right click on the library and register tables. I have 5 views in the database schema when i am trying to register i am able to see only one view out of five.

I kindly request to help me with this as it is urgent.

Thanks and Regards,

Karthik

Re: Unable to see all the views/Tables while registering a library in SAS SMC.

SASKiwi — Fri, 22 Apr 2016 01:05:15 GMT

How long are the view names you can't see? EG will not show view or table names greater than 32 characters long. Special characters may cause problems also

Re: Unable to see all the views/Tables while registering a library in SAS SMC.

Karthikk — Fri, 22 Apr 2016 04:04:45 GMT

I Have two views which are of 32 long named and 3 more than that. I am trying to register in SAS SMC i am not using SAs EG. Will that be a problem in SAS SMC also??

Unfortunately in two views i am not able to see the one which is lesser than the one which i can see

Regards,

Karthik

Re: Unable to see all the views/Tables while registering a library in SAS SMC.

SASKiwi — Fri, 22 Apr 2016 04:28:41 GMT

Yes, registering in SMC won't work either.

There are a couple of workarounds:

build a view in SQL Server reading the > 32 character named table/view that is no more than 32 characters long
build a SAS SQL passthru view to the > 32 character named table/view

Re: Unable to see all the views/Tables while registering a library in SAS SMC.

Karthikk — Fri, 22 Apr 2016 04:45:59 GMT

Hi Kiwi,

I Also have a doubt that if the names are exceeding 32 character, is there any other way to extract and register them? if yes please explain in detail or give me steps so that i can follow.

Thanks & Regards,

Karthik

Re: Unable to see all the views/Tables while registering a library in SAS SMC.

SASKiwi — Fri, 22 Apr 2016 10:08:34 GMT

I'm not aware of any other workarounds you can try. If you want to try creating a SAS view then here is some example code to use:

proc sql noprint;
  connect to odbc (noprompt = "server=MySQLServer;DRIVER=SQL Server Native Client 10.0;Trusted Connection=yes;DATABASE=MyDatabase;" ) ;
  create view sasuser.Short_Name as 
  select * from connection to odbc
  (SELECT *
   FROM [MyDatabase].[MySchema].[My_Very_Long_Table_Name_Longer_Than_32_Characters]
  )
  ;
  disconnect from odbc;
quit;

If you think these are not very satisfactory workarounds then I am in total agreement!

Re: Unable to see all the views/Tables while registering a library in SAS SMC.

Karthikk — Fri, 22 Apr 2016 10:21:00 GMT

Thank you Kiwi.

How can i Convert a character Coloumn Year which values 2004, 2005, 2006 etc into a date Year format in EG? same for Month('Feb') to Date format 'Feb' also??

Tnaks & Regards,

Karthik

Re: Unable to see all the views/Tables while registering a library in SAS SMC.

LinusH — Sat, 23 Apr 2016 20:55:15 GMT

You need to concatenate those so you get the date you think us appropriate and then use the input function to convert it to a SAS date.

Btw, this is a separate question so it would be better if post it in a separate thread.