There’s different client tools available for DB2. You can find a good overview here
Category: SAS
Loop a delimited macrovariabel in SAS
This shows how to loop a delimited macrovariabel.
%let Delimitor = ¤;
%let Logins = Login1¤Login2;
%let NumberOfLogins = %sysfunc(countw(&Logins., &Delimitor.));
%put Number of logins: &NumberOfLogins.;
%macro _CreateLogin;
%do J=1 %to &NumberOfLogins;
%let Login = %scan(&Logins., &J, &Delimitor.);
%CreateLogin(_Login=&Login.);
%end;
%mend;
%_CreateLogin;
Adding path to SASAUTOS
From SAS9.2 it’s possible to add a path to eg SASAUTOS. It’s done using ‘insert’ or ‘append’.
Read more about append and insert.
options insert=(sasautos=&
If you get a strange error, that insert isn’t recognized or something like that. The error might be caused because there’s spaces in the path, that your trying to insert.
Getting path for the SAS-program being executed
/* Gets path for the SAS-program being executed */
%macro GetSASProgramPath;
%qsubstr(%sysget(SAS_
%mend;
%let SASProgramPath = %GetSASProgramPath;
This will only work on Windows.
In batchmode you can get the programname with the code below. This will work in eg an AIX environment.
%let ProgramName = %sysfunc(getoption(sysin));
%put &ProgramName;
SAS SQL DB2 pass-through eg select
The SAS-code below shows you how to make SQL pass-through to DB2 and collect the result in a new table.
proc sql noprint;
connect to db2 as db(database=<Your database> user=<Your username> password=<Your password>);
create table <Dummy> as
select * from connection to db
(
select * from <schema>.<table>
);
disconnect from db;
quit;
It is also possible to use authdomain to collect credentials instead of user and password and the DB2 database has to be available to SAS.
SAS SQL DB2 pass-through eg index
The SAS code below shows you how to make SQL DB2 pass-through.
proc sql noprint;
connect to db2 (database=<Your database> user=<Your username> password=”<Your password>”);
execute
(
<Your SQL sentence>
) by db2;
disconnect from db2;
quit;
If you have your credentials stored in a SAS metadata-server it is possible to use a authdomain and skip entering username and password.
It is done using the following option in SAS:
options
metaserver=”<Your metadataserver>”
metaport=<usually 8561>
metaprotocol=bridge
metarepository=”Foundation”
;
proc sql noprint;
connect to db2 (database=<Your database> authdomain=<Your authdomain>);
execute
(
<Your SQL sentence>
) by db2;
disconnect from db2;
quit;
The database has to be available to SAS. This can eg be done through your DB2 ControlCenter.
DB2 libname in SAS
The syntax is:
libname <name> db2 database=<database> schema=<schema> user=<user> password=<password> in=<tablespace>;
Eg.
libname testname db2 database=mydb schema=myschema user=domain.com\myuser password=”1234Hello” in=mytablespace;
The connection to the database has to be created eg through the DB2 ControlCenter before it is possible to connect to it in SAS.
‘in=’ determins the tablespace where the data is placed.
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/
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/
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/
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/
product = SAS Enterprise Guide
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/
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/
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/
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/
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/
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.
==============================
You can submit questions or problems SAS Institute’s Technical Support
web site at:
http://support.sas.com/ctx/
==============================
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/
==============================
SAS Institute Technical Support