Date:         Wed, 22 Mar 2006 09:38:56 -0800
Reply-To:     "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Subject:      Re: Sub-query Retrives multiple records ???
Comments: To: Tom.Salwa@GMAIL.COM
Content-Type: text/plain; charset="US-ASCII"

Hi Tom,

A simple intersection with the activity
date in between your desired range seems
to fit your need.



data Cust_Tbl;
    input Cust_ID $ Age Acct_Open_date mmddyy10.;
    format Acct_Open_date mmddyy10.;
cards;
Fred 42 7/1/2002
Barney 40 6/15/2003
Wilma 41 11/20/2001
Betty 40 8/1/1999
;
run;

data Cust_History_Tbl;
    input Cust_ID $ transaction_date mmddyy10. AMT  TYPE $;
    format transaction_date mmddyy10.;
cards;
Fred 8/1/2002 1001 A
Barney 6/14/2003 1002 Boutside_range
Barney 6/15/2003 1003 C
Barney 6/16/2003 1004 D
Barney 6/14/2010 1005 Eoutside_range
Wilma 7/1/2005 1006 F
Betty 8/8/2004 1007 G
;
run;



    * period starting Acct_OPEN_DATE to the current_date ;
proc sql;
    create table result as
    select
           *
    from
           Cust_Tbl as a,
           Cust_History_Tbl as b
    where
           a.Cust_ID eq b.Cust_ID
      and
           b.transaction_date between
               a.Acct_Open_date and date()
    ;
quit;





Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group


Russell
Global Leaders in Multi-Manager Investing






-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Tom.Salwa@GMAIL.COM
Sent: Wednesday, March 22, 2006 7:34 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Sub-query Retrives multiple records ???


Hi !!

I have 2 tables

Cust_Tbl

Cust_ID Age Acct_Open_date  .....

and

Cust_History_Tbl

Cust_ID transaction_date  AMT  TYPE .....

This is what i intend to do. For every customer in the Cust_Tbl pull all
the transactions from the Cust_History_TBL  for the period starting
Acct_OPEN_DATE to the current_date.  The search might result in pulling
zero or more records. I was thinking of using a macro that would pull
all the records from the Cust_history_tbl and return a value of the
record counts. I am not sure if this is the right way or if there is any
other better way to do this. Please advise

%let current_date = 20062203;

data temp;
   set Cust_Tbl;
   if (Cust_ID ne ' ') then record_cnt = %macro(Cust_ID,
Acct_Open_Date,current_date); run;


Thanks,

Tom