BookmarkSubscribeRSS Feed
ScottBass
Rhodochrosite | Level 12

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;

Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
12 REPLIES 12
Ksharp
Super User
I guess you can't . use UPDATE 
or Pass-Through SQL .

LinusH
Tourmaline | Level 20

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
ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
LinusH
Tourmaline | Level 20
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
ScottBass
Rhodochrosite | Level 12

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 😉

 


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
LinusH
Tourmaline | Level 20

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
ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
ScottBass
Rhodochrosite | Level 12

Hi @Quentin,

 

I never got this to work, and ended up using explicit passthrough + SQL update statements.


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Quentin
Super User

Thanks @ScottBass.  Sorry to hear that.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
ScottBass
Rhodochrosite | Level 12

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/

https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at...

 

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

 


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Patrick
Opal | Level 21

@ScottBass

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 3422 views
  • 1 like
  • 5 in conversation