-
Notifications
You must be signed in to change notification settings - Fork 9
/
permuted-column-names.sas
89 lines (65 loc) · 1.98 KB
/
permuted-column-names.sas
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
%let ora_user = tq84;
%let ora_password = secret_garden;
%let ora_server = ora.test.renenyffenegger.ch;
libname tq84_ora
oracle
user = &ora_user
password = &ora_password
path = &ora_server
sql_functions = all
db_length_semantics_byte = no;
proc sql;
/* Create the Oracle table into which the data is loaded: */
connect using tq84_ora;
execute by tq84_ora (
create table tq84_permuted_column_names (
col_a varchar2(10),
col_b varchar2(10),
col_c varchar2(10)
)
);
quit;
data work.tq84_stage;
/* Load data into stage table.
Note the differing order of the column names. */
length
col_b $10
col_c $10
col_a $10;
col_a = 'AAA';
col_b = 'BBB';
col_c = 'CCC';
run;
proc sql;
/* Check stage data: */
select * from work.tq84_stage;
quit;
proc sql;
describe table work.tq84_stage;
quit;
/* Transfer data from stage table to Oracle table */
proc append
data=work.tq84_stage
base=tq84_ora.tq84_permuted_column_names (
bulkload = yes
bl_recoverable = no
bl_options = "errors=0, parallel=false" /* , ROWS=... */
bl_datafile = "%sysfunc(getoption(work))/tq84_append_test.dat"
bl_control = "%sysfunc(getoption(work))/tq84_append_test.ctl"
bl_log = "%sysfunc(getoption(work))/tq84_append_test.log"
bl_badfile = "%sysfunc(getoption(work))/tq84_append_test.bad"
bl_discardfile = "%sysfunc(getoption(work))/tq84_append_test.dsc"
);
run;
proc sql;
connect using tq84_ora;
select * from connection to tq84_ora
(select * from tq84_permuted_column_names);
/* Indeed: col_a is filled with AAA,
col_b with BBB and
col_c with CCC */
quit;
proc sql;
connect using tq84_ora;
execute by tq84_ora ( drop table tq84_permuted_column_names purge );
quit;