• No results found

Managing Tables in Microsoft SQL Server using SAS

N/A
N/A
Protected

Academic year: 2021

Share "Managing Tables in Microsoft SQL Server using SAS"

Copied!
12
0
0

Loading.... (view fulltext now)

Full text

(1)

Jon Javines, Kaiser Permanente, San Diego, CA Alan L Schepps, M.S., Kaiser Permanente, San Diego, CA

Yuexin Cindy Chen, Kaiser Permanente, San Diego, CA

ABSTRACT

Getting SAS and Microsoft SQL Server to work together using ODBC connection has been well documented over the years. Interestingly, fewer publications have explored the option to use SAS/ACCESS interface to Microsoft SQL Server directly. In addition to the scarce resources on this topic, the syntax used in the SQL pass-through facility is different than the ones used in PROC SQL. This makes the task of managing tables in SQL Server difficult even for advanced SAS users. As organizations and their technologies evolving rapidly, there are also times that a SAS user would face a situation when an ODBC connection is not possible or performs below acceptable level.

The primary objective of this paper is to provide a straight forward presentation of basic operations to help SAS users manage tables located on a SQL Server without the need of establishing ODBC connection. A secondary objective is to provide a smooth learning curve for SAS users whom are new to SQL pass-through facility. The examples included will provide SAS syntax to create or delete a table in a SQL server, add or remove a column from a SQL table, and update a SQL table with a SAS dataset based on a primary key. Variables used in the examples include numeric integers, numeric values with decimals, text strings, and datetime variables. A comprehensive and convenient macro is provided under the “ADVANCED” section to help SAS users to easily load SAS datasets into SQL tables.

The authors’ affiliated department has its SAS Server located in a UNIX environment. In addition to the use of SQL Server Management Studio (SSMS), SAS is employed to schedule batch jobs, to update tables in the SQL Servers, and to perform routine backup of SQL tables.

INTRODUCTION

Microsoft SQL Server is a popular choice of hosting large relational databases in many organizations, and SAS is often utilized for more in-depth data manipulation and analysis. In addition to the power for analytics, there are other advantages of using SAS, and this paper will focus on using SAS to manage tables in SQL Servers. Examples of basic data manipulation and updating SQL tables with SAS datasets will be provided.

Here is a list of basic operations covered in this paper:

1. Create a table in SQL Server 2. Add records to a table 3. Delete a record from a table 4. Add new columns to a table

5. Update a SQL table using another SQL table 6. Modify or delete columns from a table 7. Make a copy of a table in SQL Server 8. Delete a table from SQL Server

9. Append a SQL table to another SQL table 10. Read SQL tables into a SAS dataset

An advanced section near the end will demonstrate the steps needed to update a SQL table with a SAS dataset based on a unique primary key. Its corresponding ready-to-use SAS Macro is available in the “ADVANCED” section.

(2)

SOFTWARE VERSION

SAS Software Version 9.4

SAS/ACCESS Version 9.4

SQL Server Version 2012

SQL Server Management Studio (SSMS) Version 11.0.2100.60

UNIX AIX 7.1

BASICS

Here is an actual but simplified scenario that this paper will use to demonstrate concept: The SAS dataset

WORK.SurgProc_SAS shown below is extracted from a Teradata Server. This information needs to be transferred to a table SurgProc_SQL in a SQL Server for storage. Routine update to this table using SAS is desired as newer data becomes available in Teradata.

/*Two records in the initial SAS data extracted from Teradata*/

Data WORK.SurgProc_SAS;

Length MRN Opdate OpStartTime OpEndTime BMI 8. PxCode $10 PxDescription $255;

format Opdate mmddyy10. OpStartTime OpEndTime time5. BMI 8.1;

Label MRN ="Medical Record Number"

Opdate ="Operation Date"

OpStartTime ="Operation Start Time"

OpEndTime ="Operation End Time"

BMI ="Body Mass Index of the Patient"

PxCode ="ICD-9 procedure code"

PxDescription ="ICD-9 procedure code description";

MRN=9090909; OpDate="01JAN2015"d; OpStartTime="13:22"t;

OpEndTime="15:01"t; BMI=35.2; PxCode="";

PxDescription="";

Output;

MRN=5252525; OpDate="16JAN2015"d; OpStartTime="10:00"t;

OpEndTime="11:12"t; BMI=28.9; PxCode="81.54";

PxDescription="TOTAL KNEE REPLACEMENT";

Output;

Run;

0. TEST SAS TO SQL CONNECTION

It is important to first check and establish a connection to the SQL Server using SAS:

%let dsrc="SQL_Database_name_here";

%let schema=XXXXXXX; %*Likely to be DBO, but other values are possible;

%*If schema contains special characters,

%*Use double-quote to enclose the schema: e.g. "CS\ABC";

%let UID=XXXXXXXXXX; %*User Name;

%let PWD=XXXXXXXXXX; %*Password;

%*0. TEST SAS TO SQL CONNECTION;

Libname SQL sqlsvr datasrc=&dsrc. Schema=&schema. User=&UID. Password=&PWD.;

If the connection was not successful, please run PROC SETINIT; and check if “SAS/ACCESS Interface to Microsoft SQL Server” is licensed, then work with the SQL Server administrator to troubleshoot.

Using the LIBNAME statement is known as the implicit pass-through (Werner, 2014), which is easy to code since it uses common SAS syntax with some restrictions. This paper, however, will focus on explicit pass-through since it is more powerful especially when joining large tables within the relational database. The syntax required for the explicit pass-through is Transact-SQL which is native to the SQL Server. This means a whole new door has been opened, because now users can use SAS to run features exclusive to another environment (Riley, 2008).

(3)

1. CREATE A TABLE IN SQL SERVER

Assume that a table SurgProc_SQL should be created in the SQL Server following the same data structure as the SAS dataset WORK.SurgProc_SAS, code like this can be used:

%*1. CREATE A TABLE IN SQL SERVER;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (CREATE TABLE &schema..SurgProc_SQL (

MRN int,

OpDate datetime,

OpStartTime datetime,

OpEndTime datetime,

BMI float,

PxCode varchar(10),

PxDescription varchar(255)

CONSTRAINT SurgProc_PK01 PRIMARY KEY (MRN) )

) by mydb;

disconnect from mydb;

quit;

Please note the following:

• LIBNAME statement is not needed here. Explicit SQL pass-through facility uses “connect to SQLSVR”

statement to establish a connection to the Microsoft SQL Server.

• For easy management of data type, it is advised to practice the following:

o SAS numeric variables without decimal can be saved as “int” (integer) in SQL unless its value can be more than 2147483647.

o SAS numeric variables with decimals can be saved as “float”.

o SAS numeric date, or time variables are best converted into datetime value as text before loaded into SQL as “datetime” (See Section 2.1)

o SAS character variable can be saved as “varchar(variable length)”

o It is best practice to specify a primary key that is unique in the relational database. It can be done in SAS by providing the “CONSTRAINT”.

2. ADD RECORDS TO A TABLE

Here is a code to add the records from SAS dataset WORK.SurgProc_SAS to the SQL table SurgProc_SQL. It may seem longer than necessary, but it allows the user more control over the content that will be loaded.

The advantages of loading data this way are:

• Datetime columns are always handled correctly. No need to worry about SAS and SQL having different reference dates, or differences in handling of time variables.

• Decimals are loaded as text strings to obtain desired level of precision.

• NULL values and empty space are differentiated in character variables.

%*2. ADD RECORDS TO A TABLE;

%*2.1 Convert date and time fields to datetime value as text in SAS;

Data WORK.SurgProc_SAS_Staging (drop=Opdate_n OpStartTime_n OpEndTime_n);

set SurgProc_SAS

(rename=(Opdate=Opdate_n OpStartTime=OpStartTime_n OpEndTime=OpEndTime_n));

Opdate = put(opdate_n,yymmdd10.)||" 00:00:00";

OpStartTime = put(opdate_n,yymmdd10.)||" "||put(OpStartTime_n,time.);

OpEndTime = put(opdate_n,yymmdd10.)||" "||put(OpEndTime_n,time.);

run;

(4)

%*2.2 Macro to loop records to load them in one at a time;

%macro Add_Records( From_SAS= /*Name of the SAS dataset*/, To_SQL= /*Name of the SQL table */);

Data _NULL_;

set &FROM_SAS.;

i=strip(put(_N_,8.));

call symput ("REC_cnt", strip(i));

run;

%DO i=1 %to &REC_cnt.;

%*Manage the exact values to load into SQL;

PROC TRANSPOSE DATA=&FROM_SAS.(firstobs=&i. obs=&i.) OUT=_Trans_ (rename=(_NAME_=VarName COL1=VarValue));

VAR _ALL_;

RUN;

DATA _Trans_;

length VarValue $255;

set _Trans_;

%*Handle missing values with NULL;

if strip(VarValue)="" or strip(VarValue)="." then VarValue="NULL";

else VarValue=catt("'", strip(VarValue), "'");

RUN;

%*Add the record into SQL;

Proc Sql noprint;

Select strip(VarName) into :VarName separated by ", "

from _Trans_;

Select strip(VarValue) into :VarValue separated by ", "

from _Trans_;

quit;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (insert into &schema..&To_SQL. (&varname.) values (&VarValue.)

) by mydb;

disconnect from mydb;

quit;

%end;

%mend Add_Records;

%Add_Records(From_SAS=WORK.SurgProc_SAS_Staging, To_SQL=SurgProc_SQL);

3. DELETE A RECORD FROM A TABLE

If the record to delete can be identified by a column in the SQL table, please try this:

%*3. DELETE A RECORD FROM A TABLE;

%*3.1 Delete a record based on a value in a column;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute ( DELETE

from &schema..SurgProc_SQL where MRN=9090909

) by mydb;

disconnect from mydb;

quit;

(5)

In reality however, it is more likely that the deletion is based on another table. Assuming that another SQL table TO_EXCLUDE contains MRNs that should be removed from table SurgProc_SQL, the following code can be used:

%*3.2 Create table TO_EXCLUDE for demonstration;

proc sql;

Connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (CREATE TABLE &schema..TO_EXCLUDE (MRN int) ) by mydb;

execute (insert into &schema..TO_EXCLUDE (MRN) values (5252525) ) by mydb;

disconnect from mydb;

quit;

%*3.3 Delete a record based on value in another table;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute ( DELETE

from &schema..SurgProc_SQL

where MRN in (select MRN from &schema..TO_EXCLUDE) ) by mydb;

disconnect from mydb;

quit;

4. ADD NEW COLUMNS TO A TABLE

Assume that patient age and gender are new data elements needed in the SQL table, this code can be used to add them to the existing table SurgProc_SQL:

%*4. ADD NEW COLUMNS TO A TABLE;

%*4.1 If you have been following this instruction and have ran codes listed under Section 3.1 and 3.3, table SurgProc_SQL is likely to be empty. Rerun this macro shown in Section 2.2 to add the record back to table SurgProc_SQL;

%Add_Records(From_SAS=WORK.SurgProc_SAS_Staging, To_SQL=SurgProc_SQL);

%*4.2 Add new columns to a table;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute ( ALTER TABLE &schema..SurgProc_SQL

ADD Age Int,

Gender Varchar(1) ) by mydb;

disconnect from mydb;

quit;

5. UPDATE A SQL TABLE USING ANOTHER SQL TABLE

Assume that age and gender are available by MRN in another SQL table SurgProc_Demog. This code can be used to update the table SurgProc_SQL by MRN.

%*5. UPDATE A SQL TABLE USING ANOTHER SQL TABLE;

%*5.1 Create table SurgProc_DEMOG for demonstration;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (CREATE TABLE &schema..SurgProc_DEMOG

( MRN int,

Age Int,

Gender Varchar(1) )

) by mydb;

execute (insert into &schema..SurgProc_DEMOG (MRN, Age, Gender) values (5252525, 56, 'M'), (9090909, 67, 'F') ) by mydb;

disconnect from mydb;

quit;

(6)

%*5.2 Update Age and Gender in table SurgProc_SQL using table SurgProc_DEMOG;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (UPDATE &schema..SurgProc_SQL

SET &schema..SurgProc_SQL.Age = &schema..SurgProc_DEMOG.Age,

&schema..SurgProc_SQL.Gender = &schema..SurgProc_DEMOG.Gender FROM &schema..SurgProc_SQL

LEFT JOIN &schema..SurgProc_DEMOG

ON (&schema..SurgProc_SQL.MRN = &schema..SurgProc_DEMOG.MRN) ) by mydb;

disconnect from mydb;

quit;

6. MODIFY OR DELETE COLUMNS IN A TABLE

Assume that patient age should be captured with decimals, but it is currently defined as an integer in the SQL table.

Also, assume that the gender variable length of one is too short. This code below can change age’s property to float, and increase the length of gender to six in table SurgProc_SQL:

%*6.MODIFY OR DELETE COLUMNS IN A TABLE;

%*6.1 Modify column property;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute ( ALTER TABLE &schema..SurgProc_SQL ALTER COLUMN Age float ALTER TABLE &schema..SurgProc_SQL ALTER COLUMN Gender Varchar(6) ) by mydb;

disconnect from mydb;

quit;

Here is the code in case patient age and gender are no longer needed in the SQL table;

%*6.2 Delete columns from a table;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute ( ALTER TABLE &schema..SurgProc_SQL DROP COLUMN Age, Gender

) by mydb;

disconnect from mydb;

quit;

7. MAKE A COPY OF A TABLE IN SQL SERVER

Making a backup copy of a SQL table can be accomplished easily in SAS as follows:

%*7. MAKE A COPY OF A TABLE IN SQL SERVER;

%*7.1 Make an exact backup copy;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute ( Select *

into &schema..SurgProc_SQL_BK from &schema..SurgProc_SQL ) by mydb;

disconnect from mydb;

quit;

(7)

%*7.2 make a copy with structure only and no data;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (Select *

into &schema..SurgProc_SQL_BK2 from &schema..SurgProc_SQL where 0=1

) by mydb;

disconnect from mydb;

quit;

8. DELETE A TABLE FROM SQL SERVER

Assuming that the backup copy is no longer needed, here is how to delete it:

%*8. DELETE A TABLE FROM SQL SERVER;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (drop table &schema..SurgProc_SQL_BK ) by mydb;

disconnect from mydb;

quit;

9. APPEND A SQL TABLE TO ANOTHER SQL TABLE

Assume that the table SurgProc_SQL_BK2 is created with no data (See Section 7.2), here is how records can be copied from the table SurgProc_SQL to the SurgProc_SQL_BK2:

%*9. APPEND A SQL TABLE TO ANOTHER SQL TABLE;

%let Varname=MRN, OpDate, OpStartTime, OpEndTime, BMI, PxCode, PxDescription;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (insert into &schema..SurgProc_SQL_BK2 (&VarName.) select &VarName.

from &schema..SurgProc_SQL ) by mydb;

disconnect from mydb;

quit;

10. READ SQL TABLES INTO A SAS DATASET

Here is how two SQL tables can be joined and read into a SAS dataset using explicit pass-through. Please note that this method is a lot faster than using implicit pass-through via LIBNAME.

%*10. READ SQL TABLES INTO A SAS DATASET;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

Create table WORK.SurgProc as select *

from connection to mydb

(select S.MRN, S.Opdate, S.OpStartTime, S.OpEndTime,

D.Age, D.Gender, S.BMI, S.PxCode, S.PxDescription from &schema..SurgProc_SQL as S

left join &schema..SurgProc_DEMOG as D on (S.MRN = D.MRN) where S.Opdate > cast('01/15/2015' as datetime)

);

disconnect from mydb;

quit;

(8)

ADVANCED

After the creation of SQL table SurgProc_SQL, assume that a month has elapsed and there are new records extracted by SAS from Teradata that need to be loaded into this table. Please notice that OpStartTime and OpEndTime are in datatime format already for easier data load into SQL Server.

%*ADVANCED: New data becomes available a month later: first record MRN=9090909 was extracted again, but with some of the information corrected. Second record MRN=7373737 is brand new and need to add to the SQL table;

Data WORK.Procedure2;

MRN=9090909; OpDate="01JAN2015"d;

OpStartTime="01JAN2015 09:12"dt; OpEndTime="01JAN2015 10:31"dt;

BMI=35.0; PxCode="81.54";

PxDescription="TOTAL KNEE REPLACEMENT";

output;

MRN=7373737; OpDate="24JAN2015"d;

OpStartTime="24JAN2015 07:44"dt; OpEndTime="24JAN2015 09:20"dt;

BMI=22.1; PxCode="81.54";

PxDescription="TOTAL KNEE REPLACEMENT";

output;

format Opdate mmddyy10. OpStartTime OpEndTime datetime.;

Run;

Here is a list of tasks needed to accomplish this using the basics learned earlier:

Make a backup copy of the table SurgProc_SQL and name it SurgProc_SQL_BK (See Section 6.1)

Create a template table _tmp_ in SQL to host the new data. (See Section 1 or Section 6.2)

Add the records from SAS dataset WORK.Procedure2 to SQL table _tmp_ after converting date and time. (See Section 2.1)

Delete any record from table SurgProc_SQL table in SQL if the MRN (primary key) also exists in the newly loaded table _tmp_. (See Section 3.3)

Append records from table _tmp_ to table SurgProc_SQL (See Section 9)

Delete table _tmp_ since it is no longer needed. (See Section 8)

View a list of new MRNs added to table SurgProc_SQL using table SurgProc_SQL_BK as reference. (See Section 10)

These tasks are all embedded in the macro below. Once macro parameters are provided, it should conveniently load a given SAS dataset into an assigned table in the Microsoft SQL Server. (NOTE: Please copy the macro below separately by page. This will avoid an error message such as “[Error] Failed to transcode data from U_UTF8_CD to U_LATIN1_CE….” or accidentally including the header and footer within this macro.)

%macro SAS_to_SQL( From=/*Name of the SAS dataset*/, TO= /*Name of the SQL table*/,

Key= /*Name of the unique primary key in SQL table*/);

%*ENTER SQL Server configuration here;

%*let dsrc="SQL_Database_name_here";

%*let schema=XXXXXXX;

%*let UID=XXXXXXXXXX;

%*let PWD=XXXXXXXXXX;

%*Establish a LIBNAME Connection for %sysfunc(exist()) functions (See Section 0);

libname SQL sqlsvr datasrc=&dsrc. schema=&schema. user=&UID. password=&PWD.;

(9)

%*Check if the backup table already exist;

%*If so, drop it, so a new one can be created (See Section 8);

%if %sysfunc(exist(SQL.&TO._BK)) %then %do;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (drop table &schema..&TO._BK ) by mydb;

disconnect from mydb;

quit;

%end;

%*Check if the table &TO. exist;

%*if so, make a backup copy of the table &TO., name it with suffix _BK (See Section 7.1);

%if %sysfunc(exist(SQL.&TO.)) %then %do;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (Select *

into &schema..&TO._BK from &schema..&TO.

) by mydb;

disconnect from mydb;

quit;

%end;

%*Get info from SAS dataset &FROM.;

%*Convert datetime fields as needed in an automated fashion;

proc contents data= &FROM.

out= DS_Var (keep= memname type name format length varnum) noprint;

run;

proc sort data= DS_Var;

by varnum;

run;

Proc sql noprint;

Select name into :all_vars separated by "," from DS_Var;

quit;

%put all_vars is now: |&all_vars.|;

%*get the date or time or datetime fields into macro variables;

data DS_Var2;

set DS_Var;

where index(upcase(format),"DATE") or index(upcase(format),"TIME" )

or index(upcase(format),"YY") or index(upcase(format),"MM") or index(upcase(format),"DD");

i+1;

ii = left(put(i,best.)) ; call symput("var"||ii,trim(left(name ))) ;

call symput("fmt"||ii,trim(left(format ))) ; call symput("totv" ,trim(left(ii))) ; run;

(10)

%*if there are date or time variable, convert them into datetime text first;

%*Convert Date, DateTime, Time to datetime text for input (See Section 2.1);

%if %symexist(totv) %then %do;

data WORK.SAS_tmp (drop=org_:);

set &FROM.;

%do i = 1 %to &totv.;

if &&var&i.^=. and "&&fmt&i." in ("DATE" "MMDDYY" "DDMMYY" "YYMMDD") then &&var&i.._C = put(&&var&i.,yymmdd10.)||" 00:00:00";

else if &&var&i.^=. and "&&fmt&i."="TIME"

then &&var&i.._C = "1900-01-01 "||put(&&var&i,time.);

else if &&var&i.^=. and "&&fmt&i."="DATETIME"

then &&var&i.._C = put(datepart(&&var&i),yymmdd10.)

||" "||put(timepart(&&var&i),time.);

rename &&var&i..=org_&&var&i.. &&var&i.._C=&&var&i..;

%end; %* i = 1 to &totv.;

run;

%end; %* %symexist(totv) is true;

%else %do;

data WORK.SAS_tmp;

set &FROM.;

run;

%end; %* %symexist(totv) is false;

%*Check if the table _tmp_ already exist. If so, drop it, so a new one can be created (See Section 8);

%if %sysfunc(exist(SQL._tmp_)) %then %do;

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (drop table &schema.._tmp_

) by mydb;

disconnect from mydb;

quit;

%end;

%*Make a new template table _tmp_ with structure of table &TO. (See Section 7.2);

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (Select *

into &schema.._tmp_

from &schema..&TO.

where 0=1 ) by mydb;

disconnect from mydb;

quit;

%*Add the records from SAS dataset SAS_tmp to SQL table _tmp_. (See Section 2.2);

%Add_Records(From_SAS=WORK.SAS_tmp, To_SQL=_tmp_);

%*Delete any record from table &TO. if the (primary key) &KEY also exists in table _tmp_. (See Section 3.3);

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (DELETE

from &schema..&TO.

where &KEY. in (select &KEY.

from &schema.._tmp_) ) by mydb;

disconnect from mydb;

quit;

(11)

%*Append records from table _tmp_ to table &TO (See Section 9);

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (insert into &schema..&TO. (&all_vars.) select &all_vars.

from &schema.._tmp_

) by mydb;

disconnect from mydb;

quit;

%*Delete table _tmp_ since it no longer needed. (See Section 8);

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

execute (drop table &schema.._tmp_

) by mydb;

disconnect from mydb;

quit;

%*Display a list of new &KEY. added to table &TO. using table &TO._BK as the reference (See Section 10);

proc sql;

connect to SQLSVR as mydb (datasrc=&dsrc. user=&UID. password=&PWD.);

Create table WORK.NEW_KEY_Added as

select distinct &KEY.

from connection to mydb

( select N.&KEY.

from &schema..&TO. as N

left join &schema..&TO._BK as O on (N.MRN = O.MRN)

where O.&KEY. is NULL );

disconnect from mydb;

quit;

%mend SAS_to_SQL;

%SAS_to_SQL( From=WORK.Procedure2, TO=SurgProc_SQL, Key=MRN);

CONCLUSION

Managing tables in SQL Server using SAS may not seem straight-forward, but once a user masters the basics, it is easy to apply the same knowledge to manage other databases such as Oracle or Teradata. In fact, SAS is the top tool to integrate multiple data sources for analysis and reporting. It is worth mentioning that any SAS codes can be scheduled to run in batch mode without any human intervention. Once a data integration job is scheduled and deployed, it is likely to last for a long time with little maintenance. Any time investment in learning about how SAS integrates with other technologies will certainly be rewarded with efficiencies.

(12)

REFERENCES

Werner, Nina L. (2014) SAS® PASSTHRU to Microsoft SQL Server using ODBC. Paper SA-03-2014. Available from: http://www.mwsug.org/proceedings/2014/SA/MWSUG-2014-SA03.pdf [Accessed 8th July 2015]

Groeneveld, Jim. (2010) Updating an MS SQL database from SAS®. PhUSE 2010, Paper CC04. Available from:

http://phusewiki.org/docs/2010/2010%20PAPERS/CC04%20Paper.pdf [Accessed 8th July 2015]

• Riley, Candice. (2008) Getting SAS® to Play Nice With Others: Connecting SAS® to Microsoft SQL Server Using an ODBC Connection. SAS Global Forum 2008 Paper 135-2008. Available from:

http://www2.sas.com/proceedings/forum2008/135-2008.pdf [Accessed 8th July 2015]

SAS Institute Inc. 2013. SAS® 9.4 SQL Procedure User's Guide. Cary, NC: SAS. Institute Inc.

SAS Institute Inc. 2014. SAS/ACCESS® 9.4 for Relational Databases: Reference, Sixth Edition. Cary, NC: SAS Institute Inc.

• Transact-SQL Syntax Conventions (Transact-SQL) © 2015 Microsoft Corporation. All rights reserved.

CONTACT INFORMATION

Your comments and questions are valued and encouraged. Contact the author at:

Name: Yuexin Cindy Chen

Enterprise: Kaiser Permanente, Surgical Outcomes and Analysis Address: 8954 Rio San Diego Drive, Ste 406

City, State ZIP: San Diego, CA 92108 Work Phone: 858 / 637-6712

Fax: 858 / 637-6758

E-mail: yuexin.x.chen@kp.org Web: http://implantregistries.kp.org

SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Other brand and product names are trademarks of their respective companies.

References

Related documents

Create temp table used table from select query uses these tables using temporary tables sql server provide details with us on?. To the table is one for longer used sql server using

Within SQL*PLUS, a user can create, alter, or drop tables, to insert tuples to a table, delete tu- ples from a table, update tuples in a table, and to query the database using

Explicit PASSTHRU will execute T-SQL code syntax inside SQL Server, while implicit PASSTHRU will use your SAS DATA step or PROC SQL code.. Several useful options will

The survey applications use SAS through PROC SQL to read data from an existing SQL Server database or SAS macro language to update or insert data into SQL Server tables..

Lesson: Using Data Collector Introducing Data Collector Data Collector Concepts Configuring Data Collector Demo - Configuring Data Collector Lab Demonstration: Exercise 1

In rows inserted into statement on tables returns a different schemas you specify for example if needed by from sql select schema table a table with you create a procedural api...

Regardless of queries outside of a virus, server delete table sql server database, choose from sql of records from others learn sql server article, how to create tables.. IT pros

Following table statement is delete column server developers, i update operation is the nice thing about this tutorial teaches you use the database engineer and losing data..