Hi, I submitted this: options sastrace=',,,d' sastraceloc=saslog nostsuffix;
* allocate SQL Server library ;
libname TMP odbc
NOPROMPT="Driver={SQL Server Native Client 10.0};Server=SVDCMHPRRLSQD01;Database=RLCS_dev;Trusted_Connection=yes;"
bulkload=yes
schema=tmp
;
* drop any existing tables ;
proc datasets lib=tmp nowarn nolist;
delete source target;
quit;
* create dummy data ;
data work.target tmp.target;
set sashelp.class;
where name between 'A' and 'Q';
run;
data work.source tmp.source;
set sashelp.class;
where name between 'J' and 'Z';
if 'J' le name le 'Q' then age=99;
if 'J' le name le 'K' then sex='U';
keep name age sex;
run;
* get working code in SAS first ;
proc datasets lib=work nowarn nolist;
modify target;
index create name;
quit;
data work.target;
* read record from source table ;
set work.source (rename=(Age=src_Age Sex=src_Sex));
* look up matching target record ;
modify work.target key=name;
select(_IORC_);
* Match ;
when (%sysrc(_SOK)) do;
Age=src_Age;
Sex=src_Sex;
replace;
end;
* Insert ;
when (%sysrc(_DSENOM))do;
Age=src_Age;
Sex=src_Sex;
output;
_error_=0;
end;
* Otherwise... ;
otherwise do;
put "ERR" "OR: " _iorc_=;
stop;
end;
end;
put _iorc_= / _all_ /;
run;
* now try getting this to work in SQL Server ;
proc sql noprint;
connect using tmp;
execute (
CREATE UNIQUE CLUSTERED INDEX [IXC_TARGET_NATURAL_KEYS] ON [tmp].[target]
(
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) by tmp;
quit;
data tmp.target;
* read record from source table ;
set tmp.source (rename=(Age=src_Age Sex=src_Sex));
* look up matching target record ;
modify tmp.target (dbkey=(Name)) key=dbkey;
select(_IORC_);
* Match ;
when (%sysrc(_SOK)) do;
Age=src_Age;
Sex=src_Sex;
replace;
end;
* Insert ;
when (%sysrc(_DSENOM))do;
Age=src_Age;
Sex=src_Sex;
output;
_error_=0;
end;
* Otherwise... ;
otherwise do;
put "ERR" "OR: " _iorc_=;
stop;
end;
end;
put _iorc_= / _all_ /;
run; And got this: 1 The SAS System 09:07 Wednesday, December 7, 2016
1 ;*';*";*/;quit;run;
2 OPTIONS LS=120 PS=50;
3 OPTIONS PAGENO=MIN;
4 %LET _CLIENTTASKLABEL='Program';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTNAME='';
7 %LET _SASPROGRAMFILE=;
8
9 ODS _ALL_ CLOSE;
10 OPTIONS DEV=ACTIVEX;
11 GOPTIONS XPIXELS=0 YPIXELS=0;
12 FILENAME EGSR TEMP;
13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14 STYLE=HtmlBlue
15 STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/6.1/Styles/HtmlBlue.cs
15 ! s")
16 NOGTITLE
17 NOGFOOTNOTE
18 GPATH=&sasworklocation
19 ENCODING=UTF8
20 options(rolap="on")
21 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22
23 GOPTIONS ACCESSIBLE;
24 options sastrace=',,,d' sastraceloc=saslog nostsuffix;
25
26 * allocate SQL Server library ;
27 libname TMP odbc
ODBC: AUTOCOMMIT is NO for connection 0
28 NOPROMPT=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
28 ! XXXXXXXX
29 bulkload=yes
30 schema=tmp
31 ;
NOTE: Libref TMP was successfully assigned as follows:
Engine: ODBC
Physical Name:
32
33 * drop any existing tables ;
ODBC: AUTOCOMMIT is NO for connection 1
ODBC: Called SQLTables with schema of tmp
34 proc datasets lib=tmp nowarn nolist;
35 delete source target;
36 quit;
NOTE: Deleting TMP.source (memtype=DATA).
2 The SAS System 09:07 Wednesday, December 7, 2016
ODBC_1: Prepared: on connection 1
SELECT * FROM "tmp"."source" WHERE 0=1
ODBC_2: Executed: on connection 1
DROP TABLE "tmp"."source"
NOTE: Deleting TMP.target (memtype=DATA).
ODBC_3: Prepared: on connection 1
SELECT * FROM "tmp"."target" WHERE 0=1
ODBC_4: Executed: on connection 1
DROP TABLE "tmp"."target"
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.06 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 335.12k
OS Memory 13604.00k
Timestamp 12/07/2016 09:21:03 AM
37
38 * create dummy data ;
39 data work.target tmp.target;
40 set sashelp.class;
41 where name between 'A' and 'Q';
42 run;
ODBC_5: Prepared: on connection 1
SELECT * FROM "tmp"."target" WHERE 0=1
ODBC: AUTOCOMMIT is NO for connection 2
ODBC_6: Executed: on connection 2
CREATE TABLE "tmp"."target" ("Name" varchar(8),"Sex" varchar(1),"Age" float,"Height" float,"Weight" float)
ODBC: COMMIT performed on connection 2.
NOTE: There were 15 observations read from the data set SASHELP.CLASS.
WHERE (name>='A' and name<='Q');
NOTE: The data set WORK.TARGET has 15 observations and 5 variables.
NOTE: Compressing data set WORK.TARGET increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: The data set TMP.target has 15 observations and 5 variables.
3 The SAS System 09:07 Wednesday, December 7, 2016
NOTE: DATA statement used (Total process time):
real time 0.06 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 642.37k
OS Memory 13860.00k
Timestamp 12/07/2016 09:21:03 AM
43
44 data work.source tmp.source;
45 set sashelp.class;
46 where name between 'J' and 'Z';
47 if 'J' le name le 'Q' then age=99;
48 if 'J' le name le 'K' then sex='U';
49 keep name age sex;
50 run;
ODBC_7: Prepared: on connection 1
SELECT * FROM "tmp"."source" WHERE 0=1
ODBC: AUTOCOMMIT is NO for connection 2
ODBC_8: Executed: on connection 2
CREATE TABLE "tmp"."source" ("Name" varchar(8),"Sex" varchar(1),"Age" float)
ODBC: COMMIT performed on connection 2.
NOTE: There were 14 observations read from the data set SASHELP.CLASS.
WHERE (name>='J' and name<='Z');
NOTE: The data set WORK.SOURCE has 14 observations and 3 variables.
NOTE: Compressing data set WORK.SOURCE increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: The data set TMP.source has 14 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 387.43k
OS Memory 13860.00k
Timestamp 12/07/2016 09:21:03 AM
51
52 * get working code in SAS first ;
53 proc datasets lib=work nowarn nolist;
54 modify target;
55 index create name;
4 The SAS System 09:07 Wednesday, December 7, 2016
NOTE: Simple index Name has been defined.
56 quit;
NOTE: MODIFY was successful for WORK.TARGET.DATA.
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 139.46k
OS Memory 13860.00k
Timestamp 12/07/2016 09:21:03 AM
57
58 data work.target;
59 * read record from source table ;
60 set work.source (rename=(Age=src_Age Sex=src_Sex));
61
62 * look up matching target record ;
63 modify work.target key=name;
64
65 select(_IORC_);
66 * Match ;
67 when (%sysrc(_SOK)) do;
68 Age=src_Age;
69 Sex=src_Sex;
70 replace;
71 end;
72
73 * Insert ;
74 when (%sysrc(_DSENOM))do;
75 Age=src_Age;
76 Sex=src_Sex;
77 output;
78 _error_=0;
79 end;
80
81 * Otherwise... ;
82 otherwise do;
83 put "ERR" "OR: " _iorc_=;
84 stop;
85 end;
86 end;
87 put _iorc_= / _all_ /;
88 run;
_IORC_=0
Name=James src_Sex=U src_Age=99 Sex=U Age=99 Height=57.3 Weight=83 _ERROR_=0 _IORC_=0 _N_=1
5 The SAS System 09:07 Wednesday, December 7, 2016
_IORC_=0
Name=Jane src_Sex=U src_Age=99 Sex=U Age=99 Height=59.8 Weight=84.5 _ERROR_=0 _IORC_=0 _N_=2
_IORC_=0
Name=Janet src_Sex=U src_Age=99 Sex=U Age=99 Height=62.5 Weight=112.5 _ERROR_=0 _IORC_=0 _N_=3
_IORC_=0
Name=Jeffrey src_Sex=U src_Age=99 Sex=U Age=99 Height=62.5 Weight=84 _ERROR_=0 _IORC_=0 _N_=4
_IORC_=0
Name=John src_Sex=U src_Age=99 Sex=U Age=99 Height=59 Weight=99.5 _ERROR_=0 _IORC_=0 _N_=5
_IORC_=0
Name=Joyce src_Sex=U src_Age=99 Sex=U Age=99 Height=51.3 Weight=50.5 _ERROR_=0 _IORC_=0 _N_=6
_IORC_=0
Name=Judy src_Sex=U src_Age=99 Sex=U Age=99 Height=64.3 Weight=90 _ERROR_=0 _IORC_=0 _N_=7
_IORC_=0
Name=Louise src_Sex=F src_Age=99 Sex=F Age=99 Height=56.3 Weight=77 _ERROR_=0 _IORC_=0 _N_=8
_IORC_=0
Name=Mary src_Sex=F src_Age=99 Sex=F Age=99 Height=66.5 Weight=112 _ERROR_=0 _IORC_=0 _N_=9
_IORC_=0
Name=Philip src_Sex=M src_Age=99 Sex=M Age=99 Height=72 Weight=150 _ERROR_=0 _IORC_=0 _N_=10
_IORC_=1230015
Name=Robert src_Sex=M src_Age=12 Sex=M Age=12 Height=72 Weight=150 _ERROR_=0 _IORC_=1230015 _N_=11
_IORC_=1230015
Name=Ronald src_Sex=M src_Age=15 Sex=M Age=15 Height=72 Weight=150 _ERROR_=0 _IORC_=1230015 _N_=12
_IORC_=1230015
Name=Thomas src_Sex=M src_Age=11 Sex=M Age=11 Height=72 Weight=150 _ERROR_=0 _IORC_=1230015 _N_=13
_IORC_=1230015
Name=William src_Sex=M src_Age=15 Sex=M Age=15 Height=72 Weight=150 _ERROR_=0 _IORC_=1230015 _N_=14
NOTE: There were 14 observations read from the data set WORK.SOURCE.
NOTE: The data set WORK.TARGET has been updated. There were 10 observations rewritten, 4 observations added and 0
observations deleted.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 350.50k
OS Memory 13860.00k
6 The SAS System 09:07 Wednesday, December 7, 2016
Timestamp 12/07/2016 09:21:03 AM
89
90 * now try getting this to work in SQL Server ;
91 proc sql noprint;
92 connect using tmp;
ODBC: AUTOCOMMIT is YES for connection 2
93 execute (
94 CREATE UNIQUE CLUSTERED INDEX [IXC_TARGET_NATURAL_KEYS] ON [tmp].[target]
95 (
96 [Name] ASC
97 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
97 ! DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
98 ) by tmp;
ODBC_9: Executed: on connection 2
CREATE UNIQUE CLUSTERED INDEX [IXC_TARGET_NATURAL_KEYS] ON [tmp].[target] ( [Name] ASC )WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
99 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 100.68k
OS Memory 13860.00k
Timestamp 12/07/2016 09:21:03 AM
100
ODBC: AUTOCOMMIT is NO for connection 2
ODBC_10: Prepared: on connection 2
SELECT * FROM "tmp"."source"
101 data tmp.target;
ODBC: AUTOCOMMIT is NO for connection 3
ODBC: COMMIT performed on connection 3.
102 * read record from source table ;
103 set tmp.source (rename=(Age=src_Age Sex=src_Sex));
104
105 * look up matching target record ;
106 modify tmp.target (dbkey=(Name)) key=dbkey;
ERROR: MEMBER level locking for UPDATE, RANDOM or multiple-sequential-pass access cannot be supported under the current
isolation level.
107
7 The SAS System 09:07 Wednesday, December 7, 2016
108 select(_IORC_);
109 * Match ;
110 when (%sysrc(_SOK)) do;
111 Age=src_Age;
112 Sex=src_Sex;
113 replace;
114 end;
115
116 * Insert ;
117 when (%sysrc(_DSENOM))do;
118 Age=src_Age;
119 Sex=src_Sex;
120 output;
121 _error_=0;
122 end;
123
124 * Otherwise... ;
125 otherwise do;
126 put "ERR" "OR: " _iorc_=;
127 stop;
128 end;
129 end;
130 put _iorc_= / _all_ /;
131 run;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 211.71k
OS Memory 13860.00k
Timestamp 12/07/2016 09:21:04 AM
132
133
134 GOPTIONS NOACCESSIBLE;
135 %LET _CLIENTTASKLABEL=;
136 %LET _CLIENTPROJECTPATH=;
137 %LET _CLIENTPROJECTNAME=;
138 %LET _SASPROGRAMFILE=;
139
140 ;*';*";*/;quit;run;
141 ODS _ALL_ CLOSE;
142
143
144 QUIT; RUN;
8 The SAS System 09:07 Wednesday, December 7, 2016
145 There's nothing in the sastrace output that is giving me any clues. Like I said in the OP, I have other alternatives, including a pure-SQL Server solution: * must be in SS format - lose the colon between date and time ;
%let update_datetime= 25DEC2015 00:00:00; * usually this is "today" ;
%let scd2_end_datetime=31DEC9999 23:59:59;
%let varlist=Name Sex Age Height Weight current_record;
%put &=varlist;
%let keys=
Name
;
%let change_columns=
Sex
Age
Height
Weight
;
* Note: current_record removed from the list of scd2_columns ;
%let scd2_columns=
valid_from
valid_to
;
%macro keys(prefix1,prefix2);
%if (&__iter__ gt 1) %then AND;
&prefix1..&word=&prefix2..&word
%mend;
* target ;
%let table=tmp.target;
proc sql noprint;
connect using tmp;
execute (
TRUNCATE TABLE &table;
DBCC CHECKIDENT (%squote(&table), RESEED, 1) WITH NO_INFOMSGS;
INSERT INTO &table (
%seplist(&keys),%seplist(&change_columns),%seplist(&scd2_columns)
)
SELECT
%seplist(&keys),%seplist(&change_columns),%seplist(&scd2_columns)
FROM tmp.dummy_target;
UPDATE &table
SET DimensionCheckSum =
BINARY_CHECKSUM(%seplist(&change_columns))
) by tmp;
quit;
* source ;
%let table=tmp.source;
proc sql noprint;
connect using tmp;
execute (
TRUNCATE TABLE &table;
INSERT INTO &table (
%seplist(&keys),%seplist(&change_columns)
)
SELECT
%seplist(&keys),%seplist(&change_columns)
FROM tmp.dummy_source
UPDATE &table
SET DimensionCheckSum =
BINARY_CHECKSUM(%seplist(&change_columns))
) by tmp;
quit;
* all processing is via SQL pass-through ;
* SQL Server MERGE statement is used to process the SCD2 changes ;
* all the processing is on SQL Server via the MERGE statement ;
proc sql noprint;
connect using tmp;
execute (
BEGIN
DECLARE @LastChangeDate as DATETIME2;
SET @LastChangeDate = %squote(&update_datetime);
INSERT INTO tmp.target
(
%seplist(&keys),%seplist(&change_columns),%seplist(&scd2_columns),DimensionCheckSum
)
SELECT
%seplist(&keys),%seplist(&change_columns),%seplist(&scd2_columns),DimensionCheckSum
FROM
(
MERGE into tmp.target AS target
USING
(
SELECT
%seplist(&keys),%seplist(&change_columns),DimensionCheckSum
FROM tmp.source
) AS source
(
%seplist(&keys),%seplist(&change_columns),DimensionCheckSum
) ON
(
%loop(&keys,mname=keys(target,source))
)
WHEN MATCHED
AND target.DimensionCheckSum <> source.DimensionCheckSum
AND target.current_record='Y'
THEN
UPDATE SET
valid_to=DATEADD(SECOND,-1,@LastChangeDate)
,current_record='N'
,LastUpdated=GetDate()
,UpdatedBy=suser_sname()
WHEN NOT MATCHED THEN
INSERT
(
%seplist(&keys),%seplist(&change_columns),valid_from,valid_to,DimensionCheckSum
)
VALUES
(
%seplist(&keys,prefix=source.),%seplist(&change_columns,prefix=source.),@LastChangeDate,%squote(&scd2_end_datetime),source.DimensionCheckSum
)
WHEN NOT MATCHED BY SOURCE
AND target.current_record='Y'
THEN
UPDATE SET
valid_to=DATEADD(SECOND,-1,@LastChangeDate)
,current_record='N'
,LastUpdated=GetDate()
,UpdatedBy=suser_sname()
OUTPUT
$action
,%seplist(&keys,prefix=source.)
,%seplist(&change_columns,prefix=source.)
,source.DimensionCheckSum
,@LastChangeDate
,%squote(&scd2_end_datetime)
)
AS changes
(
/* no prefix */
action
,%seplist(&keys)
,%seplist(&change_columns)
,DimensionCheckSum
,%seplist(&scd2_columns)
)
/*
-- Name is NULL for deletes
*/
WHERE action='UPDATE' AND name IS NOT NULL;
END
) by tmp;
quit;
* to view in EG ;
proc sort data=tmp.target out=workspde.target_sorted;
by name valid_from;
run;
data workspde.target;
set tmp.target;
by name valid_from;
format _all_;
informat _all_;
attrib _all_ label=" ";
run;
* work.target is from previous program ;
proc compare base=work.target (keep=&varlist) compare=workspde.target (keep=&varlist);
run; This all started with me investigating various approaches to implement SCD2 processing, and trying to benchmark which approaches performed the best, were simplest to implement/understand/maintain, etc., etc. The complete EG project is at https://github.com/scottbass/SAS/blob/master/EGProjects/V6.1/SCD2%20Processing%20-%20Template.egp. @Ksharp Yes I have other approaches, including explicit pass through. See V10 of the EG project, which may in fact be the most performant for SQL Server tables (but perhaps hardest to understand). As per my original post, this is as much an academic exercise to see if I can get the modify statement to work with SQL Server tables. And so far my attempts are failing 😉
... View more
