SAS – Page 5 – Ryslander.com

SAS ODBC-connection directly in SAS

The SAS libname statement below shows you how to make an ODBC-connection to an SQL-server through ODBC without first having to create the ODBC-connection in the OS.

libname <Name of SAS-libname> ODBC NOPROMPT="DRIVER=SQL Server; SERVER=<Name of server>; DATABASE=<Name of database on server>; TRUSTED_CONNECTION=yes" schema=<Name of schema in database> access=readonly;

‘TRUSTED_CONNECTION=yes‘ tells SAS, that you want to use the AD to provide the password. Remember to grant the AD-user the correct right to the data.

‘access=readonly‘ is done for precaution. If the user is only suppose to read the data then this should be set on the datasource – not on the libname.

It’s also possible to use a username and password instead of ‘TRUSTED_CONNECTION=yes‘ and AD-user priviliges.

libname <Name of SAS-libname> ODBC NOPROMPT="DRIVER=SQL Server; SERVER=<Name of server>; DATABASE=<Name of database on server>; UID=<name of user>; PWD=<Password for user>" schema=<Name of schema in database> access=readonly;

It’s also possible to make SQL pass-through without having an ODBC-connection in the operating system (OS).

proc sql noprint;

connect to odbc (NOPROMPT="DRIVER=SQL Server; SERVER=<Your server>; DATABASE=<Your DB>; TRUSTED_CONNECTION=yes");execute ( <Your SQL sentence> ) by odbc;disconnect from odbc; quit;

‘TRUSTED_CONNECTION=yes‘ tells SAS, that you want to use the AD to provide the password. Remember to grant the AD-user the correct right to the data.

It’s also possible to use a username and password instead of ‘TRUSTED_CONNECTION=yes‘ and AD-user priviliges.

proc sql noprint;

connect to odbc (NOPROMPT="DRIVER=SQL Server; SERVER=<Your server>; DATABASE=<Your DB>; UID=<Your username>; PWD=<Your password>"); execute ( <Your SQL sentence> ) by odbc; disconnect from odbc;quit;

It’s also possible to make a pass-through connection using an DSN created eg in the ‘Data Sources (ODBC)’ in Windows.

proc sql noprint;

connect to odbc (DSN=<Your DSN name> USER=<Your username>; Password=<Your password>);
execute (

<Your SQL sentence>

) by odbc; disconnect from odbc; quit;

Below is a link to an article from SAS explaining different ways of using SAS ODBC-connection directly in SAS . It stats that NOPROMT can’t be used with the SQL Server ODBC Driver, but I haven’t experienced that.

Usage Note 52777: Examples of SAS/ACCESS® Interface to ODBC LIBNAME code used to access a Microsoft SQL Server database without configuring an ODBC data source name
http://support.sas.com/kb/52/777.html

Use SAS with multiple cores in a CPU – and other hardware considerations

SAS does use multiple cores in some of it’s procedures eg. PROC SORT, PROC SQL (order by and group by) and others. The use of parallel execution was introduced with SAS version 9. A description of how SAS uses threads can be found here.

It is possible to test the use and impact of executing SAS using multiple cores in parallel by using the OPTION NOTHREADS. This option tells SAS, not to use parallel execution when possible. OPTION THREADS is on by default. It is also possible to do some experimenting with OPTIONS CPUCOUNT where it is possible to set the number of cores for SAS to use in it’s execution of the SAS-program.
Experience says that SAS works best in a 4 core environment. Which probably has something to do with Amdahls law. That is of course not the case if your system handles multiple users or you start multiple SAS-processes eg. through the use of RSUBMIT with multiple tasks in one SAS-session or a script that starts multiple SAS-sessions.
If you are operating in an Intel environment the type of processor you use can have a big impact on the performance of your SAS-program. Intel XEON X5000-series performs a lot better than the Intel XEON X7000-series. Rule of thumb is that for big jobs a high clockfrequency is preferable.

Another rule of thumb is to use 4GB of RAM per core on a 64bit system and 2GB of RAM per core on a 32bit system of course depending on your individual use of the available memory. Paging of the memory should of course be avoided.

Reading from SSD disks connected to the server is of course very fast but still expensive considering that you probably need some sort of RAID configuration. Writing temporary files and log files to SSD disks could give a performance advantage, but with long seriel writes cheaper disk maybe connected through a SAN might give a better performance than SSD disks.

SAS S-Notes

S-Notes from SAS Institute will give you an overview of knowledgebase notes and possible hotfixes for different SAS-products.

http://support.sas.com/techsup/news/snotes.html

The mail that you will receive on a daily basis will look something like this. It is not possible to subscribe to specific SAS-products. All SAS-products is contained in the mail.

SNOTES-L update for 27JAN2012

****************************
New or Revised SAS Notes
****************************

product = Base SAS

43817 – A %INCLUDE statement for an externally allocated data set might fail in the z/OS environment
Release(s) Reported:
Release(s) Fixed: 9.3
Date Created: 25JUL2011
Date Last Updated: 26JAN2012
http://support.sas.com/kb/43817.html

45507 – FILEEXIST causes an infinite loop on Unix if path equates to the root directory
Release(s) Reported: 9.21_M3
Release(s) Fixed:
Date Created: 26JAN2012
Date Last Updated: 26JAN2012
http://support.sas.com/kb/45507.html

45503 – Metaserver and metaport options are not valid with the SAS Information Maps LIBNAME engine when the code is included in a SAS Stored Process
Release(s) Reported: 9.21
Release(s) Fixed:
Date Created: 26JAN2012
Date Last Updated: 26JAN2012
http://support.sas.com/kb/45503.html

product = SAS Add-in for Microsoft Office

45501 – Information maps with incorrectly truncated data item names cannot be opened in SAS Enterprise Guide and the SAS Add-In for Microsoft Office
Release(s) Reported: 4.3
Release(s) Fixed:
Date Created: 26JAN2012
Date Last Updated: 26JAN2012
http://support.sas.com/kb/45501.html

product = SAS Enterprise Guide

product = SAS Financial Management

45359 – Number formatting styles might be ignored in SAS Financial Management reports or data-entry forms
Release(s) Reported: 5.2
Release(s) Fixed: 5.3
Date Created: 11JAN2012
Date Last Updated: 26JAN2012
http://support.sas.com/kb/45359.html

45320 – Red/pink cells displayed in SAS Financial Management Web-based data entry form
Release(s) Reported: 5.2
Release(s) Fixed: 5.3
Date Created: 09JAN2012
Date Last Updated: 26JAN2012
http://support.sas.com/kb/45320.html

45367 – Time dimension is not drillable in a SAS Financial Management Web data entry form
Release(s) Reported: 5.2
Release(s) Fixed: 5.3
Date Created: 11JAN2012
Date Last Updated: 26JAN2012
http://support.sas.com/kb/45367.html

product = SAS Warranty Analysis

41253 – SAS Warranty Analysis rich client launch fails with Error: Unexpected exception: java.lang.NullPointerException
Release(s) Reported: 4.2
Release(s) Fixed:
Date Created: 15OCT2010
Date Last Updated: 26JAN2012
http://support.sas.com/kb/41253.html

Search SAS Problem Notes, Usage Notes, and Samples on the Technical Support Web Site:
You can go to SAS Institute’s Technical Support web site search page at:
http://support.sas.com/kb
to search for these and other notes. Alternatively, if your mail program
allows it, you can simply click on the url below the title to view them
via your browser.
==============================

===================================================Problem/Question Submission:
You can submit questions or problems SAS Institute’s Technical Support
web site at:
http://support.sas.com/ctx/supportform/index.jsp
=================================================================================Unsubscribing from the SNOTES-L Listserv:
To unsubscribe you can send mail to LISTSERV@LISTSERV.SAS.COM with:
‘SIGNOFF snotes-l’
as the only text in the body of the message (without the single quotes).
You can also unsubscribe via the web at:
http://support.sas.com/techsup/news/snotes.html
=================================================================================Supportively,
SAS Institute Technical Support

OS Scheduler in SAS Management Console (SMC)

Defining an operating system scheduling server in the metadata repository

In SAS Management Console, right-click Server Manager and select New Server. Fill out the next windows of the wizard with this information:
a. Scheduling Servers: Operating System Services
b. Scheduling server name: OS Scheduling Server
c. SubType: Windows
d. Control Directory: eg. C:\SAS\Config\Lev1\SchedulingServer
e. Command: eg. C:\SAS\Config\Lev1\SchedulingServer\SchedulingServer.bat
f. Object Server Parameters: applevel=2
g. Authentication Domain: DefaultAuth
h. Host Name field: <Server>
i. Port number: 8451
j. Select Finish when you have entered all the necessary information.

Associating the operating system scheduling server with a spawner definition

In SAS Management Console, right-click the object spawner defined in Server Manager and select Properties.
In the Spawner Properties dialog box, select the Servers tab.
From the Available Servers list, select the operating system scheduling server and move it to the Selected Servers list.
Click OK.
Restart the services for the object spawner eg through services.msc on the OS.

It might also be nessessary to allow inbound connections in OS firewall on port 8451 (this port might be different on your SAS-system).