Can I update a SQL Server table in place using the modify statement?

Reply
Super Contributor
Posts: 374

Can I update a SQL Server table in place using the modify statement?

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;
Grand Advisor
Posts: 9,576

Re: Can I update a SQL Server table in place using the modify statement?

I guess you can't . use UPDATE 
or Pass-Through SQL .

Esteemed Advisor
Posts: 5,198

Re: Can I update a SQL Server table in place using the modify statement?

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.

Data never sleeps
Super Contributor
Posts: 374

Re: Can I update a SQL Server table in place using the modify statement?

"You don't have access to DI Studio?".  Nope Smiley Happy

 

"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.

Esteemed Advisor
Posts: 5,198

Re: Can I update a SQL Server table in place using the modify statement?

If you issue
Options sastrace = ',,,d' SASTRACELOC = saslog nostsuffix;
you will hopefully see in the log what SQL is being generated and you can take it from there.
Data never sleeps
Super Contributor
Posts: 374

Re: Can I update a SQL Server table in place using the modify statement?

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 Smiley Wink

 

Esteemed Advisor
Posts: 5,198

Re: Can I update a SQL Server table in place using the modify statement?

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?

Data never sleeps
Super Contributor
Posts: 374

Re: Can I update a SQL Server table in place using the modify statement?

Thanks @LinusH.  If I get this to work or create a SAS TS ticket I'll post the results.

Ask a Question
Discussion stats
  • 7 replies
  • 280 views
  • 0 likes
  • 3 in conversation