Date:         Thu, 15 Dec 2005 11:59:14 -0500
Reply-To:     Charles Harbour <charles.harbour@PEARSON.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Charles Harbour <charles.harbour@PEARSON.COM>
Subject:      Re: Using SAS to access SQL Server's @@ROWCOUNT system variable
Comments: To: groovyfitzgeralds@GMAIL.COM

Very Cool!  Quite literally, I was researching rowcount when the email
alert came in.  I'm sure I'll put this to good use shortly.

Thanks for posting.

CH

On Thu, 15 Dec 2005 08:33:37 -0800, Matt <groovyfitzgeralds@GMAIL.COM>
wrote:

>Good morning,
>
>Originally I wasn't able to directly put the contents of the SQL Server
>system variable @@ROWCOUNT into a macro variable, however to get around
>this I created a tmp table [tmpRowCnt] on sql server, updated a table,
>updated @@ROWCOUNT into [tmpRowCnt] and then created &rowCount from the
>contents of [tmpRowCnt].rowCount.
>
>Here's a code example and log with the results:
>
>%put
>======================================================================;
>%put Renumber Express Plus;
>%put
>======================================================================;
>libname db odbc dsn=cmr_prod;
>%put Create tmpTable of Bosses to be renumbered;
>data db.tmpBossRenum;
>        set BossRenum;
>run;
>%put Create tmpTable to capture the @@Rowcount;
>data tmpRowCnt;
>        format rowCnt 15.;
>        rowCnt=0;
>run;
>data db.tmpRowCnt;
>        set tmpRowCnt;
>run;
>%put Update Contract and Boss numbers from tmp Table;
>proc sql noprint;
>        connect to odbc("DSN=CMR_prod");
>        %put Update Boss in EPBenefitGroup;
>        execute
>        (update EPBenefitGroup
>        set Boss=b.newBoss
>        from EPBenefitGroup a, [tmpBossRenum] b
>        where a.Boss=b.Boss)
>        by odbc;
>        * Report number of records updated;
>                execute (update tmpRowCnt set rowCnt=@@rowcount) by odbc;
>                select rowCnt into: rowCount from db.tmpRowCnt;
>                %put Number of Bosses updated: &rowCount;
>        ....
>        %put Update Boss in EPBossLines;
>        execute
>        (update EPBossLines
>        set Boss=b.newBoss
>        from EPBossLines a, [tmpBossRenum] b
>        where a.Boss=b.Boss)
>        by odbc;
>        * Report number of records updated;
>                execute (update tmpRowCnt set rowCnt=@@rowcount) by odbc;
>                select rowCnt into: rowCount from db.tmpRowCnt;
>                %put Number of Bosses updated: &rowCount;
>        ....
>quit;
>%put Drop Tmp Table;
>proc sql;
>        drop table db.tmpBossRenum;
>        drop table db.tmpRowCnt;
>quit;
>%put Clear libname;
>libname db clear;
>
>
 ===========================================================================
==============================================
>Log:
>
 ===========================================================================
==============================================
>======================================================================
>Renumber Express Plus
>======================================================================
>NOTE: Libref DB was successfully assigned as follows:
>      Engine:        ODBC
>      Physical Name: cmr_prod
>
>NOTE: SAS variable labels, formats, and lengths are not written to DBMS
>tables.
>NOTE: There were 7 observations read from the data set WORK.BOSSRENUM.
>NOTE: The data set DB.tmpBossRenum has 7 observations and 2 variables.
>NOTE: DATA statement used:
>      real time           0.00 seconds
>      cpu time            0.00 seconds
>
>NOTE: The data set WORK.TMPROWCNT has 1 observations and 1 variables.
>NOTE: DATA statement used:
>      real time           0.00 seconds
>      cpu time            0.00 seconds
>
>NOTE: There were 1 observations read from the data set WORK.TMPROWCNT.
>NOTE: The data set DB.tmpRowCnt has 1 observations and 1 variables.
>NOTE: DATA statement used:
>      real time           0.00 seconds
>      cpu time            0.00 seconds
>
>Update Contract and Boss numbers from tmp Table
>Update Contract in EPContractBenefit
>Number of Contracts updated:              241
>Update Boss in EPContractBenefit
>Number of Bosses updated:              187
>Update Boss in EPBenefitGroup
>Number of Bosses updated:               21
>Update Boss in EPBGFactor
>Number of Bosses updated:               62
>Update Boss in EPBossLines
>Number of Bosses updated:              556
>Update Boss in EPExpressWBFormulaOverride
>Number of Bosses updated:                9
>Update Boss in EPExpressWBMisc
>Number of Bosses updated:                3
>
>NOTE: PROCEDURE SQL used:
>      real time           1.39 seconds
>      cpu time            0.04 seconds
>
>Drop Tmp Table
>NOTE: Table DB.tmpBossRenum has been dropped.
>NOTE: Table DB.tmpRowCnt has been dropped.
>NOTE: PROCEDURE SQL used:
>      real time           0.00 seconds
>      cpu time            0.00 seconds