Date:         Thu, 15 Dec 2005 10:07:52 -0800
Reply-To:     progger1.0@GMAIL.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         progger1.0@GMAIL.COM
Organization: http://groups.google.com
Subject:      Re: Using SAS to access SQL Server's @@ROWCOUNT system variable
Comments: To: sas-l@uga.edu
In-Reply-To:  <1134664417.350944.66200@g44g2000cwa.googlegroups.com>
Content-Type: text/plain; charset="iso-8859-1"

This is amazing!!! Thanks, I have been looking for something like this
for the longest time! You are a GENIUS!!!!
Matt 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