The What, When and Why of SAS/ACCESS

[#1]

I often speak about SAS/ACCESS to customers. One particular presentation I gave introduced the key characteristics and capabilities of SAS/ACCESS interfaces by answering these questions:

• What are SAS/ACCESS interfaces?
• What capabilities do they provide?
• When and why should they be used?

Here are some highlighted questions from the Q&A segment held at the end of the presentation for ease of reference. I’ve attached the slides as well.

How does SAS integrate with Microsoft SQL Server?

There are 3 SAS/ACCESS modules for Microsoft SQL Server:

1. SAS/ACCESS Interface to ODBC
2. SAS/ACCESS Interface to Microsoft SQL Server for Windows and UNIX (this includes SAS Access/ODBC and an ODBC driver for MS SQL Server
3. SAS/ACCESS to OLE DB

Note that SAS/ACCESS to ODBC can be used to access other data sources and targets beyond SQL Server, and it also supports bulk loading on several platforms, so its versatility should be considered if you have diverse data sources and targets.

Is SAS/ACCESS included with Enterprise Guide and/or Enterprise Miner? How can I  determine which SAS/ACCESS interfaces I have?

It depends on your licenses; PROC SETINIT will reveal your licenses. Enterprise Guide contains capabilities that open Microsoft files such as Access and Excel without additional SAS/ACCESS licensing. Bear in mind that SAS/ACCESS interfaces for different databases are often licensed separately or are included with other packages, such as SAS Office Analytics or SAS Data Management Standard/Advanced.

How does the new DS2 feature in version 9.4 compare to implicit and explicit SQL?

DS2 conducts parallel processing of the DATA step for version 9.4; it is part of Base SAS and it’s a great way to boost performance of the DATA step.  It also include FedSQL, which can run SQL queries within a DATA step.  It supports in database for the following if you have the SAS Code Accelerator product for one of these Databases.

Hadoop

Teradta

Netezza

Greenplum(Pivotal)

Otherwise you can still process SAS data sets in parallel.

Implicit and explicit SQL is using Proc SQL for in database processing.  It will require SAS/Access for you database.

Q: ­When you don't use in-database processing, does ALL the data need to come over  from the database to the SAS side to process in SAS?  Even if you have a subsetting where clause, it won't actually filter until all the data is moved to the SAS server correct?

A: ­That is correct­.  You have to use SQL passthrough in order to have the processing occur on the DBMS side.

Q: ­What is the opposite of passthrough?­

A: ­Not processing anything inside a database­.

Q: ­Does SAS/Share help update SAS data set when its being read by some other process?

A: ­Yes, it manages locks and concurrent read/write at the record level.

Q: ­Do we need separate license for that? ­

A: ­It depends on how the products in your license are bundled - some packages and solutions contain SAS/SHARE. An easy way to see what is in your license is to submit: PROC SETINIT; RUN; on your SAS environments.

Q: ­So, I can have in-database processing database using a libname statement, or a "from connection to" in the proc sql, right?­

A: ­Yes­.

Q: ­It seems like, when I read in PDW data, as soon as I use libname, even if my code is ANSI sql, it seems to want to send all the data over to SAS.  Why is that?­

A: ­if you are using functions/operations that only occur in SAS, such as modeling PROC REG, only SAS can process that.  Therefore, you sacrifice in-DB processing. Turn on SAStrace to see what's happening.

 Q: ­Where is the sqlgeneration system option or where do we add it?­

A:http://support.sas.com/documentation/cdl/en/lesysoptsref/66899/HTML/default/viewer.htm#n1ag2fud7ue3al...  

 http://support.sas.com/documentation/cdl/en/hostwin/63047/HTML/default/viewer.htm#p0drw76qo0gig2n1kc...

Q: ­How can we tell if the option SQLGENERATION is turned on?  How can we default this is always be on?­ 

A: Options SQLgeneration;

     To verify that it is turned on, submit:  Proc Options; run; 

Q: ­And that tracing again is options sastrace ',,,d'   right?­

A: ­You are correct.

Q: ­Can you look at this log and tell me why I cannot connect to the server? 97   proc sql;

98   connect to oracle(user=mxf32

98 ! password="&dbpass" path=mpdw);

SYMBOLGEN:  Macro variable DBPASS resolves to {SAS002}AA6B5E4F15E4EBCC4089DEA520476F0F358ABEB20­

A­: I would not be able to tell unless I see a specific error message:  Is the value of &dbpass correct?  Try hardcoding that value to test.  I suggest opening a Technical Support track. 

Q: ­Bulk loading data into DBMS temp tables is slow comparatively to permanent table, Why?­

A: There is no general answer for that unless we have more information from the SAS log and the DBMS log.  Please contact SAS Technical Support regarding this.

Q: ­Which is recommended- ODBC or SAS/ Access to ODBC interface?­

A: ­ODBC drivers are provided by many vendors to provide read/write capabilities to their respective data formats. SAS/ACCESS to ODBC is how SAS reads/writes to non-SAS data sources and targets via those drivers­.

Q: ­Does Studio have any DBMS options mentioned for EG?­

A: ­No - you can control SAS Studio via your SAS code.

Q: ­Can you use SAS Access to connect to a mainframe­?

A: ­Base SAS on a mainframe includes read/write to flat files and VSAM and PDS.  If you want to get to a specific DBMS such as DB2, Sybase, etc. you will need a SAS/Access product for the DBMS.

Q: ­Can you review again the libname options that let you know if the code is performing in-database processing?­

A: Please see the included slide deck.

Q: ­In terms of DS2, he mentioned that it can translate ANY SAS function to SQL?­

A: Pretty much all SAS functions will be translated via the code accelerator for the specific DBMS.d

[NZ_Hockey_Mum]

Hi Jeff

I am having trouble connecting to an Azure SQL database using the OLE DB drivers. 

I can connection to another database on the same server using the same credentials but the database I am trying to connect to has two dashes and a dot in the name for example bitA-bitB-BitC.BitD as the database name. I can use the same credtentials to log directly on the database so the credtentials is not the issue.

This works (different database on the same server):

LIBNAME zDEV_tt OLEDB    PROPERTIES="'initial catalog'=databaseName" 
  PROVIDER=MSOLEDBSQL 
  DATASOURCE="servername.database.windows.net" 
  SCHEMA=schemaNameA
  USER=UserName 
  PASSWORD="encoded password" ;

This does not:

LIBNAME zDEV_tt OLEDB    PROPERTIES=('initial catalog'="bitA-bitB-BitC.BitD")
  PROVIDER=MSOLEDBSQL 
  DATASOURCE="servername.database.windows.net" 
  SCHEMA=schemaNameB 
  USER=UserName 
  PASSWORD="encoded password" ;

Any ideas?

Kind Regards

Michelle

PS this is an open track with SAS support at the moment

[AnnaBrown]

Hi @NZ_Hockey_Mum,

I'm glad to hear you've opened a SAS Technical Support track, because that's likely the best route for your specific scenario. A search on the community came up with several discussions on connecting to Azure, and one points to a SAS note on the topic. Perhaps some guidance there may be helpful.

You can also open a New Message in the SAS Data Management Community to see if any experts there have input.

All the best,

Anna

[DoubleTing]

Hello there!
I would like to ask the following error when using the proc nlmixed process for cross-layer interaction testing when doing a two-level logistic regression model:

ERROR: Quadrature accuracy of 0.000100 could not be achieved with 31 points. The achieved accuracy was 1.000000.

How should I do?

My level 2 explanatory variables are multi-class variables.

The sas code is as follows:

data xb;
d_0=h_0*entity;
d_2=h_2*entity;
d_3=h_3*entity;
proc nlmixed;
parms B0=-1.3365 B_g=0.4312 B_age=-0.09340 B_e=1 B_o0=0.4114,B_o2=-1.6674,B_o3=0.2509,B_o4=3.1463 B_h0=0.5347,B_h2=0.7353,B_h3=0.1027,V0=0.6416,v1=0,cov=1 B_d0=1 B_d2=1 B_d3=1;
z=B0+B_g*gender+B_age*age1+B_e*entity+B_o0*o_0+B_o2*o_2+B_o3*o_3+B_o4*o_4+B_h0*h_0+B_h2*h_2+B_h3*h_3
+B_d0*d_0+B_d2*d_2+B_d3*d_3+u0j+u1j*entity;
if(result=1) then p=1/(1+exp(-z));
else p=1-(1/(1+exp(-z)));
LL=log(p);
Model result~general(P);*定义结局变量及其分布;
random u0j u1j~normal([0,0],[v0,cov,v1])subject=doctor;
estimate 'Var(u)' V0;
estimate 'Var(u1)' V1;
run;

Is my code wrong?