Date:         Mon, 7 Aug 2006 17:15:25 +0000
Reply-To:     toby dunn <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby dunn <tobydunn@HOTMAIL.COM>
Subject:      Re: Merging
Comments: To: irinfigvam@YAHOO.COM
In-Reply-To:  <20060807170315.89555.qmail@web34104.mail.mud.yahoo.com>
Content-Type: text/plain; format=flowed

Irin ,

If you want the sum variable(s) to be summed by more than just variable then
yes.

Toby Dunn

Normal People Worry Me!!!!!

I reject your reality and substitute my own!!!





From: Irin later <irinfigvam@YAHOO.COM>
Reply-To: Irin later <irinfigvam@YAHOO.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Merging
Date: Mon, 7 Aug 2006 10:03:14 -0700

Toby, in case I would have chosen SQL syntax...shouldn't I  use

   Group By Member , all other fields....

   (mot only by member)as long as I use aggregate function sum()?

   Irin

toby dunn <tobydunn@hotmail.com> wrote:
   I am mor confused as to what you actually are trying to do here. The code
is doing exactly what you are telling it too and exactly how it is
documented.


data memlevelMed;
merge allclaimsMed (in=d) memlevelM (in=f) ;
by member;
if d=1 and f=0;
run;

The If condition will only select observation which comes from allclaimsmed
and not from memlevelm. Since memlevelm is derived using proc means from
allclaimsmed it will have all values of member in allclaimsmed.

What I think you want is summary variable attched to your allclaimsmed data
set where you sum allow_sum by member.

proc means
data =allclaimsMed noprint NWay ;
by member;
var allow_amt;
output out=memlevelM sum=allow_sum;
run;

data memlevelMed;
merge allclaimsMed
memlevelM ;
by member;
run;

Since allclaimsmed is sorted by member to begin with and you use it as a by
variable memlevelm will come out in the correct sort sequence. So there is
no need for the two sorts.


If it was me I would look into a SQL solution which seems to be built to do
just what you want.

Proc SQL ;
Create Table MemLevelMed As
Select * , Sum( Allow_Sum ) as Sums
From AllClaimsMed
Group By Member ;
Quit ;




Toby Dunn

Normal People Worry Me!!!!!

I reject your reality and substitute my own!!!



---------
On Mon, 7 Aug 2006 08:08:27 -0700, Irin later wrote:

 >I run the following code:
 >
 > proc means data =allclaimsMed noprint;
 > by member;
 > var allow_amt;
 > output out=memlevelM sum=allow_sum;
 > run;
 >
 > proc sort data=memlevelM;
 > by member;
 > run;
 >
 > proc sort data=allclaimsMed;
 > by member;
 > run;
 >
 > data memlevelMed;
 > merge allclaimsMed (in=d) memlevelM (in=f) ;
 > by member;
 > if d=1 and f=0;
 > run;
 >
 > I am confused as I got memlevelMed empty while both tables allclaimsMed
and memlevelM are not empty and as it is seen above memlevelM is based on
allclaimsMed
 >
 > (Actually when I do if d=1 and f=1; the result is inappropriate)
 >
 > What I am doing wrong?
 >
 >
 >---------------------------------
 >Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+
countries) for 2¢/min or less.





---------------------------------
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls.  Great rates
starting at 1&cent;/min.