Preserves the Microsoft SQL Server GUID (Unique Identifier).
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Access |
| Default: | YES [ODBC, Microsoft SQL Server] |
| NO [OLE DB] | |
| NO | |
| Data source: | ODBC, OLE DB, Microsoft SQL Server |
| Note: | Support for Microsoft SQL Server and ODBC was added in SAS 9.4M7. |
Table of Contents
removes the brackets from around the GUID.
retains the brackets that are around the GUID.
SAS/ACCESS can deliver Microsoft SQL Server GUIDs (unique identifiers) into SAS with or without curly brackets.
For Microsoft SQL Server and ODBC interfaces, the default is to remove the brackets from the GUID when it is read into SAS.
For OLE DB, the brackets must be removed from the GUIDs values before you execute a join. If PRESERVE_GUID=NO is specified, the join fails. See the log below for PRESERVE_GUID=NO. Use PRESERVE_GUID=YES to remove the brackets. See the log for PRESERVE_GUID=YES.
166 proc sql;
167 connect to oledb (
168 init_string="
169 Provider=SQLOLEDB.1;
170 Password=secret;
171 Persist Security Info=True;
172 User ID=user_id;
173 Initial Catalog=Pubs;
174 data source=your_data_source"
175 );
176
177 execute (drop table t) by oledb;
178 execute (CREATE TABLE T(I INT, U uniqueidentifier)) by oledb;
179 execute (INSERT T VALUES(1, '6F9619FF-8B86-D011-B42D-00C04FC964FF')) by
180 oledb;
181
182 disconnect from oledb;
183 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.01 seconds
184
185 libname mylib oledb
186 init_string="
187 Provider=SQLOLEDB.1;
188 Password=secret;
189 Persist Security Info=True;
190 User ID=user_id;
191 Initial Catalog=Pubs;
192 data source=your_data_source" preserve_guid=no ;
NOTE: Libref MYLIB was successfully assigned as follows:
Engine: OLEDB
Physical Name:
193
194 /* if preserve_guid=no then the guid value should be in brackets {}
195 ex: insert into mylib.t values (2, '{7F9619FF-8B86-D011-B42D-
00C04FC964FF}');
196
197 if preserve_guid=yes then the guid value should not be in curly brackets {}
198 ex: insert into mylib.t values (2, '7F9619FF-8B86-D011-B42D-00C04FC964FF');
199 */
200
201
202 proc sql;
203 insert into mylib.t values (2, '{7F9619FF-8B86-D011-B42D-00C04FC964FF}');
NOTE: 1 row was inserted into MYLIB.t.
204 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
205
206 proc print data=mylib.t;
207 run;
NOTE: There were 2 observations read from the data set MYLIB.t.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
208
209 proc contents data=mylib.t;
210 run;
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.04 seconds
cpu time 0.00 seconds
211
212 data x;
213 set mylib.t;
214 run;
NOTE: There were 2 observations read from the data set MYLIB.t.
NOTE: The data set WORK.X has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.06 seconds
cpu time 0.00 seconds
215
216 proc append data=x base=mylib.t;
217 run;
NOTE: Appending WORK.X to MYLIB.t.
NOTE: There were 2 observations read from the data set WORK.X.
NOTE: 2 observations added.
NOTE: The data set MYLIB.t has . observations and 2 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.06 seconds
cpu time 0.01 seconds
218
219 proc print data=mylib.t;
220 run;
NOTE: There were 4 observations read from the data set MYLIB.t.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
1 proc sql;
2 connect to oledb (
3 init_string="
4 Provider=SQLOLEDB.1;
5 Password=secret;
6 Persist Security Info=True;
7 User ID=user_id;
8 Initial Catalog=Pubs;
9 data source=your_data_source"
10 );
11
12 execute (drop table t) by oledb;
13 execute (CREATE TABLE T(I INT, U uniqueidentifier)) by oledb;
14 execute (INSERT T VALUES(1, '6F9619FF-8B86-D011-B42D-00C04FC964FF')) by
15 oledb;
16
17 disconnect from oledb;
18 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.18 seconds
cpu time 0.15 seconds
19
20 libname mylib oledb
21 init_string="
22 Provider=SQLOLEDB.1;
23 Password=secret;
24 Persist Security Info=True;
25 User ID=user_id;
26 Initial Catalog=Pubs;
27 data source=your_data_source" preserve_guid=yes ;
NOTE: Libref MYLIB was successfully assigned as follows:
Engine: OLEDB
Physical Name:
28
29 /* if preserve_guid=no then the guid value should be in curly brackets {}
30 ex: insert into mylib.t values (2, '{7F9619FF-8B86-D011-B42D-
00C04FC964FF}');
31
32 if preserve_guid=yes then the guid value should not be in curly brackets {}
33 ex: insert into mylib.t values (2, '7F9619FF-8B86-D011-B42D-00C04FC964FF');
34 */
35
36
37 proc sql;
38 insert into mylib.t values (2, '7F9619FF-8B86-D011-B42D-00C04FC964FF');
NOTE: 1 row was inserted into MYLIB.t.
39 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.06 seconds
cpu time 0.01 seconds
40
41 proc print data=mylib.t;
42 run;
NOTE: There were 2 observations read from the data set MYLIB.t.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
43
44 proc contents data=mylib.t;
45 run;
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.06 seconds
cpu time 0.03 seconds
46
47 data x;
48 set mylib.t;
49 run;
NOTE: There were 2 observations read from the data set MYLIB.t.
NOTE: The data set WORK.X has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
cpu time 0.04 seconds
50
51 proc append data=x base=mylib.t;
52 run;
NOTE: Appending WORK.X to MYLIB.t.
NOTE: There were 2 observations read from the data set WORK.X.
NOTE: 2 observations added.
NOTE: The data set MYLIB.t has . observations and 2 variables.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.06 seconds
cpu time 0.03 seconds
53
54 proc print data=mylib.t;
55 run;
NOTE: There were 4 observations read from the data set MYLIB.t.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds