SAS Access - clinicalsashub.com

SAS ACCESS :

The SAS access products allow access to data in popular DBMS systems like Oracle, Db2, Sybase, Teradata, Ms-Access etc…

Ways to perform SAS ACCESS
1) SQL PASS THRU QUERY
2) LIBNAME FACILITY
3) PROC ACCESS
4) PROC DBLOAD (Export from SAS to DBMS)

SQL PASS THRU QUERY

It is useful to connect to database for retrieving data from selected tables in database.
Syntax:-
Proc sql;
Connect to database as dbconn (user=username password=password path=path);
Create table table_name as
Select * from connection to database
(
Sql statements
);
Disconnect from database;
Quit;
In Sql pass thru query, there are 3 minimum statements those are Connect, Select, Disconnect and one optional statement that is Create.
Connect statement: Useful to connect any database by specifying connection credentials.
Select statement: Useful to select the table from database for retrieving into SAS.
Disconnect statement: Useful to disconnect from database once retrieve the data.
Create statement: Useful to create SAS dataset with retrieved data from database
Without this statement it can’t create dataset in SAS, but it retrieve the data and prints the data in output window.

Examples:-
Connecting to Oracle and retrieving the data from Oracle table to SAS DATASET.
Proc sql;
Connect to ORACLE (USER=SCOTT PASSWORD=TIGER);
Create table work.ds1 as
Select * from connection to ORACLE
(
Select * from EMP
);
Disconnect from ORACLE;
Quit;
Proc sql;
Connect to ORACLE as Krishna (USER=SCOTT PASSWORD=TIGER);
Create table work.ds1 as
Select * from connection to Krishna
(
Select EMP_ID, EMP_NAME, ADDRES, INCOME from EMP
);
Disconnect from Krishna;
Quit;

Real time examples:

CONNECTING TO ORACLE

Creating dates, Incrementing or Decrementing dates using PROC SQL
Proc sql;
Connect to oracle as Krishna (USER=system
PASSWORD=”{sas002}00E2D04E17E09782001237A6″);
Create table dat11 as
Select * from connection to Krishna
( Select to_char(to_date(SYSDATE),’dd-Month’) as today,
to_char(to_date(SYSDATE-1),’dd-Month’) as yesterday,
to_char(add_months(to_date(SYSDATE-1),-1),’dd-Mon’) as lastmonth_yday,
to_char(to_date(SYSDATE-1),’Month’) as current_month,
to_char(add_months(to_date(SYSDATE-1),-1),’Month’) as last_month,
to_char(to_date(SYSDATE-1),’dd-Mon-yyyy’) as yday from dual
);
Disconnect from Krishna;
Quit;
PROC SQL;
CONNECT TO ORACLE AS KRISHNA (USER=MISREP PASSWORD=”{sas001}QzBycDByX3Qz” PATH=SRMPROD);
CREATE TABLE EAGTN AS
SELECT * FROM CONNECTION TO KRISHNA
(SELECT CASE
WHEN PRODUCT_TYPE IN(‘B’,’C’,’S’) THEN ‘CASH_TURNOVER’
WHEN PRODUCT_TYPE IN(‘M’,’T’,’A’,’E’) THEN ‘MARGIN_MARGIN_PLUS_TURNOVER’
WHEN PRODUCT_TYPE IN(‘F’,’O’,’P’) THEN ‘DERIVATIVE_TURNOVER’ END PRODUCTS
, ROUND(SUM(CASE WHEN EAG_GROUP IN(‘PAG’) THEN TOTAL_TRADE_VALUE END)/10000000,2) EAG_TURNOVER
, ROUND(SUM(CASE WHEN EAG_GROUP IN(‘PCG’) THEN TOTAL_TRADE_VALUE END)/10000000,2) PCG_TURNOVER
, ROUND(SUM(CASE WHEN B.CLM_MTCH_ACCNT NOT LIKE ‘65%’
AND B.CLM_MTCH_ACCNT NOT LIKE ‘75%’
THEN TOTAL_TRADE_VALUE END)/10000000,2)COMP_TURNOVER
, ROUND(SUM(CASE WHEN EAG_GROUP IN(‘NRI’) THEN TOTAL_TRADE_VALUE END)/10000000,2)NRI_TURNOVER
, ROUND(SUM(CASE WHEN EAG_GROUP IN(‘PAG’,’PCG’,’NRI’) THEN TOTAL_TRADE_VALUE END)/10000000,2)TEAM_WISE_TURNOVER
, ROUND(SUM(TOTAL_TRADE_VALUE)/10000000,2) COMP_DIRECT_TURNOVER
, ROUND(SUM(CASE WHEN (B.CLM_MTCH_ACCNT LIKE ‘65%’ OR
B.CLM_MTCH_ACCNT LIKE ‘75%’) THEN TOTAL_TRADE_VALUE
END)/10000000,2)NRI_IDIRECT_TURNOVER
FROM misrep.EAG_CLM_MAP A,(SELECT * FROM CLM_DAY_SUMM)B
WHERE TRADE_DATE = TRUNC(sysdate-1)
AND B.CLM_MTCH_ACCNT = A.CLM_MTCH_ACCNT(+)
GROUP BY CASE
WHEN PRODUCT_TYPE IN(‘B’,’C’,’S’) THEN ‘CASH_TURNOVER’
WHEN PRODUCT_TYPE IN(‘M’,’T’,’A’,’E’) THEN ‘MARGIN_MARGIN_PLUS_TURNOVER’
WHEN PRODUCT_TYPE IN(‘F’,’O’,’P’) THEN ‘DERIVATIVE_TURNOVER’
END
);
DISCONNECT FROM KRISHNA;
QUIT;

CONNECTING TO ODBC

PROC SQL
CONNECT TO ODBC AS KRISHNA(DATASRC=STANIQ USER=STANMIS PASSWORD=”{SAS001}VHJHZDNSYWMZCG==”);
CREATE TABLE DAT11 AS
SELECT * FROM CONNECTION TO KRISHNA
(SELECT
DATEFORMAT(GETDATE()-1,’DD-MMM-YYYY’) AS REPDATE,
DATEFORMAT(GETDATE()-1,’DD-MMM’) AS DAY,
DATEFORMAT(CONVERT(DATE,DATEADD(MM,-1,GETDATE()-1),GETDATE()- 1),’DD-MMM’) AS LASTDAY,
DATEFORMAT(GETDATE()-1,’MMM’) AS MTH,
DATEFORMAT(CONVERT(DATE,DATEADD(MM,-1,GETDATE()-1),GETDATE()-1),’MMM’) AS LASTMTH
);
DISCONNECT FROM KRISHNA;
QUIT;
PROC SQL;
Connect to ODBC as Krishna (DATASRC=STANIQ USER=STANMIS PASSWORD=”{sas001}VHJhZDNSYWMzcg==”);
Create table acc as
Select * from connection to Krishna
(SELECT TEAM
, COUNT(CASE
WHEN DCG_ACCNT_OPEN_DT =CONVERT(DATE,GETDATE()-1,’YYYY-MM-DD’)THEN DCG_FORM_NO
END)FTD
, COUNT(CASE
WHEN DCG_ACCNT_OPEN_DT BETWEEN convert(date,DATEADD(dd,-(DAY(GETDATE()-1)-1),GETDATE()-1)) AND CONVERT(DATE,GETDATE()-1,’YYYY-MM-DD’) THEN DCG_FORM_NO
END)MTD
, COUNT(CASE
WHEN DCG_ACCNT_OPEN_DT BETWEEN ‘2010-04-01′ AND CONVERT(DATE,GETDATE()-1,’YYYY-MM-DD’)THEN DCG_FORM_NO
END)THIS_FY
FROM
( SELECT CASE
WHEN DCG_SOURCING_SUB_TEAM=’CWM’ THEN ‘CWM’
WHEN DCG_SOURCING_REF_TEAM =’PWM’ THEN ‘PWM’
WHEN DCG_SOURCING_SUB_TEAM=’PWM’ THEN ‘PWM’
WHEN DCG_SOURCING_SUB_TEAM =’DBC’ THEN ‘SUB-BROKER’
WHEN DCG_FORM_NO BETWEEN 4000000000 AND 4999999999 THEN ‘LAS’
WHEN DCG_SOURCING_TEAM =’CNL’ AND DCG_SOURCING_AGENT_CAT=’KYC’ THEN ‘CHANNEL KYC’
WHEN DCG_SOURCING_TEAM =’CNL’ AND DCG_SOURCING_AGENT_CAT=’EMP’ THEN ‘CHANNEL SM SOURCED’
WHEN DCG_SOURCING_SUB_TEAM = ‘DSA’ THEN ‘CHANNEL DSA’
WHEN DCG_SOURCING_TEAM =’CNL’ AND DCG_SOURCING_SUB_TEAM = ‘BND’ THEN ‘BANKDIRECT’
WHEN DCG_SOURCING_TEAM =’CNL’ THEN ‘OPEN MARKET’
WHEN DCG_SOURCING_TEAM = ‘CST’ THEN ‘CENTER SALES ONLINE’
WHEN DCG_SOURCING_TEAM= ‘ATS’ THEN ‘ATS OFFLINE’
WHEN DCG_SOURCING_TEAM =’COR’ THEN ‘CORPORATE SALES’
ELSE ‘OTHERS’
END TEAM,
CASE
WHEN DCG_FORM_NO BETWEEN 9000000000 AND 9999999999 THEN ‘RETAIL’
WHEN DCG_FORM_NO BETWEEN 6000000000 AND 6199999999 THEN ‘INV’
WHEN DCG_FORM_NO BETWEEN 3000000000 AND 3999999999 THEN ‘CORPORATE’
WHEN (DCG_FORM_NO BETWEEN 1000000000 AND 1999999999) OR (DCG_FORM_NO BETWEEN 6200000000 AND 6299999999) THEN ‘NRI’
WHEN DCG_FORM_NO BETWEEN 2200000000 AND 2299999999 THEN ‘ATS ONLINE’
END SUB_TEAM,
DCG_FORM_NO,DCG_ACCNT_OPEN_DT
FROM VT_DIM_CUSTOMER
WHERE DCG_ACCNT_OPEN_DT >=’2010-04-01′
)X
GROUP BY TEAM
);
Disconnect from Krishna;
Quit;

Writing the data in SAS program and exporting(Loading) into Database
Proc sql;
Connect to oracle(user=Scott password=tiger);
Execute(create table ds2 (ID number, NAME char(10), SALARY number))by oracle;
Execute(insert into ds2 values(101, ‘Ravan’, 2300))by oracle;
Execute(insert into ds2 values(102, ‘Varan’, 4500))by oracle;
Execute(insert into ds2 values(103, ‘Alfred’, 4000))by oracle;
Execute(insert into ds2 values(104, ‘Alice’, 3500))by oracle;
Execute(insert into ds2 values(105, ‘James’, 2500))by oracle;
Execute(insert into ds2 values(106, ‘David’, 4800))by oracle;
Execute(insert into ds2 values(107, ‘Jeevan’, 3400))by oracle;
Disconnect from oracle;
Quit;
Exporting (Loading) the data from Existing SAS Datasets to Database
PROC DBLOAD data=sashelp.class dbms=oracle;
User=scott;
Password=tiger;
Table=ds3;
Load;
Run;
The DBLOAD procedure enables you to create and load data into a DBMS table from a SAS data set, data file, SAS view, or another DBMS table, or to append rows to an existing table .
It also enables you to submit non-query DBMS-specific SQL statements to the DBMS from your SAS session .
Connecting to EXCEL and retrieving the data from EXCEL to SAS DATASET.
Proc sql;
Connect to excel(path=’E:\SAS\SOURCE_DATA\EXCEL\DEMO.xls’);
Create table ds4 as
Select*from connection to excel
(
Select * from sheet1
/*specify sheet name. If you are reading except first sheet specify those sheet names like [sheet2$]*/
)
;
Disconnect from excel;
Quit;
/* In above example SHEET1 is sheet name which we are importing from DEMO excel sheet*/
Connecting to EXCEL and Exporting(Loading) the data from SAS to EXCEL.
Proc sql;
Connect to excel(path=’E:\SAS\TARGET_DATA\EXCEL\DEMO.xls’);
Execute(create table ds5 (ID number, NAME char(10), SALARY number))by excel;
Execute(insert into ds5 values(101, ‘Ravan’, 2300))by excel;
Execute(insert into ds5 values(102, ‘Varan’, 4500))by excel;
Disconnect from excel;
Quit;
/* In above example ds5 is sheet name in demo excel sheet.*/

Connecting to EXCEL and Exporting(Loading) the data from existing SAS dataset to EXCEL.
PROC DBLOAD DATA=SASHELP.CLASS DBMS=EXCEL;
PATH=”D:\krishna\sas source\Excel\DEMO.XLS”;
LOAD;
RUN;
Connecting to MS-ACCESS and retrieving the data from MS-ACCESS table to SAS DATASET.
Proc sql;
Connect to access(path=’E:\SAS\SOURCE_DATA\EXCEL\DEMO.mdb’);
Create table ds6 AS
Select*from connection to access
(
Select*from EMP
)
;
Disconnect from access;
Quit;
/* In above example ds6 is table name in MS-ACCESS which we are retrieving.*/
Connecting to EXCEL and Exporting(Loading) the data from SAS to MS-ACCESS.
Proc sql;
Connect to access(path=’E:\SAS\TARGET_DATA\EXCEL\DEMO.mdb’);
Execute(create table ds7 (ID number, NAME char(10), SAL number))by access;
Execute(insert into ds7 values(101, ‘Ravan’, 2300))by access;
Execute(insert into ds7 values(102, ‘Varan’, 4500))by access;
Disconnect from access;
Quit;
/* In above example ds7 is table name which we are loading into MS-ACCESS.*/

LIBNAME FACILITY

Libname is one of the SAS/ACCESS interface to ORACLE.
It is useful to connect database for retrieving the data from database to SAS.
Syntax:-
Libname libref database <connection-options> <libname-options>;
Example:-
LIBNAME MY_SAS ORACLE USER=SCOTT PASSWORD=TIGER ;
LIBNAME: Useful to create a library.
LIBREF: It is a SAS Name with what name we are creating a library. It’s associate SAS with database Scheema, Server, Path or group of tables and views.
DATABASE: It is SAS/ACCESS engine name for interface to database like Oracle, Db2, Teradata and MS-Access etc..
Connection options: Provide connection info to connect to database like
User=Database user name: Specify a database username, if that user name contains spaces then enclose with quotes. Default username for oracle is SCOTT. In working time client will provide this username, Generally it should be your empid.
Password=Database Password: Specify an optional password for database username. In working time you will get this password from client.
Path= (or) Scheema= : Specify the location of database for retrieving selected tables from that path or schema, otherwise all the database tables retrieves into SAS Library this case we can get space issues in SAS Server to avoid this we need specify path name or schema name. At working time you can get this from database admin.

Examples:-
LIBNAME MY_SAS ORACLE USER=SCOTT PASSWORD=TIGER PATH=SRMPROD ;
LIBNAME MY_LIB DB2 USER=TSIPL0403 PASSWORD=“{sas002}DE07760058599ED64BE3BD9A0203CAAF44587DC4” SCHEEMA=DENT_DATA;
LIBNAME KRISHNA “C:\Documents and Settings\Administrator\Desktop\sas”;

PROC ACCESS

Useful to retrieve the data from different data sources to SAS like ADABAS, AS400, DATACOM, DB2, DB2VM, DB2_2, DB2_6000, DB2_UNIX, DBASE, DBF, DBIPRO, DBMGR, DIF, EXCEL, IDMS, IMS, INGRES, INGRES6, LOTUS, ORACLE, PRIME, RDB, S2K, SQLDS, SQLSERVR, SYBASE, ULTRIX, WK1, WK3, WK4, XLS.

PROC ACCESS DBMS=ORACLE;
CREATE WORK.DS1.ACCESS;
USER=SCOTT;
PASSWORD=TIGER;
TABLE=CLASS;
CREATE WORK.DS1.VIEW;
SELECT ALL;
CREATE SASUSER.DS2.VIEW;
SELECT NAME HEIGHT;
CREATE SASUSER.DS3.VIEW;
SUBSET WHERE AGE>13;
SELECT ALL;
RUN;
PROC ACCESS DBMS=XLS;
CREATE WORK.SAMPLE1.ACCESS;
PATH=’C:\Documents and Settings\Administrator\Desktop\New Folder\ALLESH.XLS’;
GETNAMES=YES;
CREATE WORK.SAMPLE1.VIEW;
SELECT ALL;
CREATE WORK.SAMPLE2.VIEW;
SELECT NAME AGE WEIGHT;
CREATE WORK.SAMPLE3.VIEW;
SUBSET WHERE SEX=’F’ and AGE>=14;
SELECT ALL;
RUN;

So what if we don’t have SAS/Access for Oracle?
We can use the ODBC drivers to connect to the ORACLE as follows
Proc sql;
Connect to ODBC (DATASRC=STANIQ USER=STANMIS PASSWORD=”{SAS001}VHJHZDNSYWMZCG==”);
Create table work.ds1 as
Select * from connection to ODBC
(
Select * from EMP
);
Disconnect from ODBC;
Quit;
OR
LIBNAME MY_LIB ODBC USER=STANMIS PASSWORD=”{SAS001}VHJHZDNSYWMZCG==” PATH=SRMPROD;

We have some automatic macro variables to display error code and error messages while connecting to database
&sysdbmsg displays the DBMS error messages
%put &sysdbmsg;
ORACLE: ORA-01017: invalid username/password; logon denied
&sysdbrc displays the DBMS error codes
59 %put &sysdbrc;
&sqlxmsg displays the DBMS error messages
%put &SQLXMSG;
ORA-12560: TNS: protocol adapter error
&sqlxrc displays the DBMS error codes
%put &SQLXRC;
-12560

Example:-
Proc sql;
Connect to ORACLE (USER=SCOTT PASSWORD=TIGER);
Create table work.ds1 as
Select * from connection to ORACLE
(
Select * from EMP
);
Disconnect from ORACLE;
Quit;
&sqlxmsg displays the DBMS error messages
%put &SQLXMSG;
&sqlxrc displays the DBMS error codes
%put &SQLXRC;

For more details read below documents

SAS Online Help Document:
http://support.sas.com/onlinedoc/913/docMainpage.jsp
SAS Forums
http://www2.sas.com/proceedings/forum2007/

Some of the frequently getting error messages with ORACLE connectivity.
The service you’re referencing is not configured in your sqlnet.ora file.
ERROR: ORACLE connection error: ORA-12154: TNS: could not resolve service name.
ERROR: Error in the LIBNAME statement.
You do not have SAS/Access for ORACLE or it’s not configured properly
ERROR: The SAS/ACCESS Interface to ORACLE cannot be loaded. The SASORA code appendage could not be loaded.
ERROR: Error in the LIBNAME statement.
You’ve entered your User ID or password incorrectly or it’s expired.
ERROR: ORACLE connection error: ORA-01017: invalid username/password;
Logon denied.
ERROR: Error in the LIBNAME statement.