Date:         Thu, 3 Feb 2011 14:00:20 -0800
Reply-To:     Sterling Paramore <gnilrets@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sterling Paramore <gnilrets@GMAIL.COM>
Subject:      Re: Out of memory?
In-Reply-To:  <941871A13165C2418EC144ACB212BDB001C62477@dshsmxoly1504g.dshs.wa.lcl>
Content-Type: text/plain; charset=ISO-8859-1

Dan,

That worked.  Unfortunately, it took 19 minutes so it performed worse than
the other two methods.

-Sterling

On Thu, Feb 3, 2011 at 1:32 PM, Nordlund, Dan (DSHS/RDA) <
NordlDJ@dshs.wa.gov> wrote:

> Sterling,
>
> I presume all claim lines for a particular claim_id are adjacent in the
> file, but claim_id is out of order.  Could you move just claim_id from the
> class statement to the BY statement and use the NOTSORTED option?  Then
> there is no need to sort.  Something like
>
> proc means noprint missing nway data = WORKERR._BMClaims_Concat;
>   by Claim_Id notsorted
>   var Claim_Counter ClaimLine_Counter ClaimLine_Paid_Amt
> ClaimLine_COB_Paid_Amt
>       Claim_Interest_Amt ClaimLine_Savings_Amt;
>   class Claim_Diag_Primary Claim_Prim_Hosp_Proc_Cd Claim_Bill_Type;
>   output out = WORK._BMClaims (drop = _TYPE_ _FREQ_)  sum()=;
> run;
>
> Dan
>
> Daniel J. Nordlund
> Washington State Department of Social and Health Services
> Planning, Performance, and Accountability
> Research and Data Analysis Division
> Olympia, WA 98504-5204
>
>
> > -----Original Message-----
> > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> > Sterling Paramore
> > Sent: Thursday, February 03, 2011 12:44 PM
> > To: SAS-L@LISTSERV.UGA.EDU
> > Subject: Re: Out of memory?
> >
> > The sort itself takes 6 minutes.  Proc sql takes 5.
> >
> > -Sterling
> >
> > On Thu, Feb 3, 2011 at 12:41 PM, toby dunn <tobydunn@hotmail.com>
> > wrote:
> >
> > >
> > > Sterling,
> > >
> > > Use a proc sort then use a DoW loop to do the summarization of your
> > > variable.  Should be as fast or faster than SQL.
> > >
> > >
> > >
> > > Toby Dunn
> > >
> > >
> > > "I'm a hell bent 100% Texan til I die"
> > >
> > >
> > > "Don't touch my Willie, I don't know you that well"
> > >
> > >
> > >
> > >
> > >
> > >
> > > ----------------------------------------
> > > > Date: Thu, 3 Feb 2011 12:28:35 -0800
> > > > From: gnilrets@GMAIL.COM
> > > > Subject: Re: Out of memory?
> > > > To: SAS-L@LISTSERV.UGA.EDU
> > > >
> > > > Dan,
> > > >
> > > > There are multiple claim lines per claim, so the point is to sum
> > the
> > > dollars
> > > > over all the claim lines and get one row per claim (the other class
> > > > variables do not change by claim line). The data does not come
> > sorted by
> > > > Claim_Id. I could sort and then do proc means with "by", but my
> > tests
> > > show
> > > > that proc sql is more than twice as fast.
> > > >
> > > > I'm mostly just freaking out because I don't know how many other
> > places
> > > I've
> > > > got similar code that could be producing empty tables without
> > giving an
> > > > ERROR!
> > > >
> > > > -Sterling
> > > >
> > > > On Thu, Feb 3, 2011 at 12:20 PM, Nordlund, Dan (DSHS/RDA) <
> > > > NordlDJ@dshs.wa.gov> wrote:
> > > >
> > > > > Sterling,
> > > > >
> > > > > Can you give us more detail about your date? My first thought
> > when I
> > > look
> > > > > at your code is: why is claim_id on the class statement? How many
> > > claim_id
> > > > > values are there? What are you trying to summarize, I mean what
> > is the
> > > main
> > > > > task here?
> > > > >
> > > > > Dan
> > > > >
> > > > > Daniel J. Nordlund
> > > > > Washington State Department of Social and Health Services
> > > > > Planning, Performance, and Accountability
> > > > > Research and Data Analysis Division
> > > > > Olympia, WA 98504-5204
> > > > >
> > > > >
> > > > > > -----Original Message-----
> > > > > > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
> > Behalf Of
> > > > > > Sterling Paramore
> > > > > > Sent: Thursday, February 03, 2011 11:55 AM
> > > > > > To: SAS-L@LISTSERV.UGA.EDU
> > > > > > Subject: Out of memory?
> > > > > >
> > > > > > Dear SAS-L,
> > > > > >
> > > > > > I assume the following is failing due to the system running out
> > of
> > > > > > memory
> > > > > > (way to go SAS - not only do you not tell me what the problem
> > is,
> > > this
> > > > > > doesn't even register as a real ERROR, only a warning) because
> > if I
> > > run
> > > > > > it
> > > > > > with an OBS= statement, it completes just fine.
> > > > > >
> > > > > > To solve the problem, I've tried first sorting the dataset (6
> > > minutes),
> > > > > > then
> > > > > > doing proc means with a by (7 minutes). I also tried using proc
> > sql
> > > > > > and it
> > > > > > required only 5 minutes.
> > > > > >
> > > > > > Any other ideas? I'd prefer not to have to rewrite a bunch of
> > my proc
> > > > > > means
> > > > > > as sql.
> > > > > >
> > > > > > Thanks,
> > > > > > Sterling
> > > > > >
> > > > > >
> > > > > >
> > > > > > 16
> > > > > > 17 proc means noprint missing nway data =
> > > > > > WORKERR._BMClaims_Concat;
> > > > > > 18 var Claim_Counter ClaimLine_Counter ClaimLine_Paid_Amt
> > > > > > ClaimLine_COB_Paid_Amt
> > > > > > 19 Claim_Interest_Amt ClaimLine_Savings_Amt;
> > > > > > 20 class Claim_Id Claim_Diag_Primary
> > > > > > Claim_Prim_Hosp_Proc_Cd
> > > > > > Claim_Bill_Type;
> > > > > > 21 output out = WORK._BMClaims (drop = _TYPE_ _FREQ_)
> > > > > > sum()=;
> > > > > > 22 run;
> > > > > >
> > > > > > NOTE: The SAS System stopped processing this step because of
> > errors.
> > > > > > NOTE: There were 70619390 observations read from the data set
> > > > > > WORKERR._BMCLAIMS_CONCAT.
> > > > > > WARNING: The data set WORK._BMCLAIMS may be incomplete. When
> > this
> > > step
> > > > > > was
> > > > > > stopped there were 0 observations and 10 variables.
> > > > > > NOTE: PROCEDURE MEANS used (Total process time):
> > > > > > real time 2:23.15
> > > > > > cpu time 3:08.01
> > > > >
> > >
>