SAS/ACCESS Interface to OLE DB |
Overview |
For general information about this feature, see Temporary Table Support for SAS/ACCESS.
Establishing a Temporary Table |
When you want to use temporary tables that persist across SAS procedures and DATA steps with OLE DB, you must use the CONNECTION=SHARED LIBNAME option. In doing so, the temporary table is available for processing until the libref is closed.
Terminating a Temporary Table |
You can drop a temporary table at any time, or allow it to be implicitly dropped when the connection is terminated. Temporary tables do not persist beyond the scope of a singe connection.
Examples |
Using the sample Internat table, this example creates a temporary table, #LONDON, with Microsoft SQL Server. It contains information about flights that flew to London. This table is then joined with a larger SQL Server table that lists all flights, March, but matched only on flights that flew to London.
libname samples oledb Provider=SQLOLEDB Password=dbigrp1 UID=dbitest DSN='lupin\sql2000' connection=shared; data samples.'#LONDON'n; set work.internat; where dest='LON'; run; proc sql; select b.flight, b.dates, b.depart, b.orig from samples.'#LONDON'n a, samples.march b where a.dest=b.dest; quit;
In this next example, a temporary table, New, is created with Microsoft SQL Server. The data from this table is then appended to an existing SQL Server table, Inventory.
libname samples oledb provider=SQLOLEDB dsn=lupinss uid=dbitest pwd=dbigrp1; data samples.inventory(DBTYPE=(itemnum='char(5)' item='varchar(30)' quantity='numeric')); itemnum='12001'; item='screwdriver'; quantity=15; output; itemnum='12002'; item='hammer'; quantity=25: output; itemnum='12003'; item='sledge hammer'; quantity=10; output; itemnum='12004'; item='saw'; quantity=50; output; itemnum='12005'; item='shovel'; quantity=120; output; run; data samples.'#new'n(DBTYPE=(itemnum='char(5)' item='varchar(30)' quantity='numeric')); itemnum='12006'; item='snow shovel'; quantity=5; output; itemnum='12007'; item='nails'; quantity=500; output; run; proc append base=samples.inventory data=samples.'#new'n; run; proc print data=samples.inventory; run;
The following example demonstrates the use of a temporary table using the SQL pass-through facility.
proc sql; connect to oledb as test (provider=SQLOLEDB dsn=lupinss uid=dbitest pwd=dbigrp1); execute (create table #FRANCE (flight char(3), dates datetime, dest char(3))) by test; execute (insert #FRANCE select flight, dates, dest from internat where dest like '%FRA%') by test; select * from connection to test (select * from #FRANCE); quit;
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.