Background:
I have a large target table in SQL Server
I have a small transactions table in SQL Server
The actual processing is SCD2 processing, where I close/insert updated records, and insert new records
I'm investigating different approaches to see what might have the best performance
Note:
This question is sort of an academic question only. I have other approaches which work. I'm not even sure this would be my final approach anyway. But I'm curious if I can update a SQL Server table in place using SAS code. I may also be confused with respect to the modify vs. update statements.
See below code. Is it possible to get the last step to work? Perhaps with UPDATE_TABLE_LOCK option? (which I couldn't get to work).
* allocate SQL Server library ;
libname TMP odbc
NOPROMPT="Driver={SQL Server Native Client 10.0};Server=MYSERVER;Database=MYDB;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;
I guess you can't . use UPDATE or Pass-Through SQL .
You don't have access to DI Studio?
There you have a SCD2 transformation, where you could explicitly mark for database processing.
For table updates using transactions, this is stated in the DI Studio doc:
"Of these three choices, the DATA step with MODIFY KEY= option often outperforms the other update methods in tests conducted on loading SAS tables. An index is required. The MODIFY KEY= option can also perform adequately for database tables when indexes are used."
So the answer is probably yes, but of course you need to test and potentially adjust.
"You don't have access to DI Studio?". Nope 🙂
"For table updates using transactions, this is stated in the DI Studio doc:
"Of these three choices, the DATA step with MODIFY KEY= option often outperforms the other update methods in tests conducted on loading SAS tables. An index is required. The MODIFY KEY= option can also perform adequately for database tables when indexes are used." "
Thanks for that. I'll point out that my previously submitted code does work for SAS tables. I was hoping to coax the Access engine to also apply this technique with SQL Server tables.
It may help to post the error message I get when using this approach with SQL Server tables:
ERROR: MEMBER level locking for UPDATE, RANDOM or multiple-sequential-pass access cannot be supported under the current
isolation level.
AFAIK, the Access engine usually converts SAS code to native SQL Server code and implicitly passes that to SQL Server. I was hoping there was a way to coax the Access engine to issue update statements under the covers, say via UPDATE_LOCK_TYPE or some other option.
It may be that I would need to use the SAS update statement rather than modify, which I don't think (???) will meet my needs.
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 😉
Given the documentation modify key= *should* work.
Some notes suggest that authorization may come into play (like having set access=readonly).
Perhaps opening ticket to SAS tech support is worth a try?
Thanks @LinusH. If I get this to work or create a SAS TS ticket I'll post the results.
Hi @ScottBass did you get this working, or settle on some other useful approach for updating SQL server records from SAS?
In the past I've rarely needed to write to SQL server, and usually when I do need to write I'm only doing inserts. I'm about to start on a project where I will need to do some updates to existing records. I can always delete a record and then insert a replacement, but for the sake of the database audit trail it'd probably be better off if I do actual updates to existing records/fields.
Thx,
-Q.
Hi @Quentin,
I never got this to work, and ended up using explicit passthrough + SQL update statements.
Thanks @ScottBass. Sorry to hear that.
Hi Quentin,
If you're doing this sort of processing and both your source and target tables are in SQL Server, I recommend learning the SQL Server MERGE statement. It took me a while to wrap my head around it, but it has proved very useful, esp. with SCD2 processing (along with the BINARY_CHECKSUM function).
Google "sql server merge example", but here are a couple hits:
https://www.red-gate.com/simple-talk/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/
Here's the official Microsoft documentation, but their examples usually suck 😉
https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql
Hope this helps,
Scott
Especially for cases where all the data already is in the data base I'm always going for a data base only approach using explict pass-through SQL end-to-end. That's in my experience almost always the most efficient way and best performant approach. It makes it also much easier to get DBA support for such cases if further tweaking is required as there is no direct SAS code involved which they don't understand.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.