|
On Wed, 22 Mar 2006 09:38:56 -0800, Terjeson, Mark (IM&R)
<Mterjeson@RUSSELL.COM> wrote:
>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;
I like this SQL solution. Another option though is the tried-and-true
traditional SAS way, like
proc sort data=Cust_Tbl out=ct;
by Cust_ID;
run;
proc sort data=Cust_History_Tbl out=cht;
by Cust_ID transaction_date;
run;
data result;
merge ct cht;
by Cust_ID;
if Acct_Open_date <= transaction_date <= date();
run;
Note that if the tables are maintained in Cust_ID order, these 2 PROC SORT
steps aren't needed.
Then, to display the counts, one could use something like:
proc freq data=result;
tables Cust_ID;
run;
[more below]
[snip]
>-----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;
I strongly encourage the use of SAS-conventional dates, like those in
Mark's sample data. They make lots of things things much easier. If for
some reason 8-digit integers (or 8-byte character strings representing 8-
digit integers) are used instead, at least design them so that they sort
and compare naturally; ie, YYYYMMDD rather than YYYYDDMM.
>
>data temp;
> set Cust_Tbl;
> if (Cust_ID ne ' ') then record_cnt = %macro(Cust_ID,
>Acct_Open_Date,current_date); run;
It doesn't work that way. A macro is not a subroutine. Fortunately, this
task can be done without a macro.
>
>
>Thanks,
>
>Tom
| 
