Date: Thu, 30 Sep 2004 15:50:15 -0400
Reply-To: "Chakravarthy, Venky" <Venky.Chakravarthy@PFIZER.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Chakravarthy, Venky" <Venky.Chakravarthy@PFIZER.COM>
Subject: Re: counting with dates using SQL problem
Content-Type: text/plain; charset="iso-8859-1"
After reading your follow up post, your sample result does not match the
requirements. Try the following reflexive join and see if this suits your
needs. SQL heads may fine tune this:
proc sql ;
create table needed (drop=flag) as
select distinct a.*, count(*) as count
, case when a.a_date <= b.b_date then 1
else 0 end as flag
from in as a, in as b
where calculated flag = 1
group by a.a_date ;
quit ;
_________________________________
Venky Chakravarthy
E-mail: swovcc_AT_hotmail_DOT_com
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of
Cynthia qiu
Sent: Thursday, September 30, 2004 2:28 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: counting with dates using SQL problem
Hi SASperts,
Can someone help me?
In the program below I'm trying to count the number of instances of a_date
where a_date is less than or equal to b_date.
This is the result I get:
id b_date a_date count
2 02JAN02 29FEB96 9
2 07MAY02 29FEB96 9
2 07JUN02 07JUN02 10
2 26NOV02 13DEC00 11
2 29NOV02 01NOV00 11
2 04DEC02 29AUG02 11
2 06DEC02 17AUG00 11
2 11DEC02 03JUL95 11
2 13DEC02 29AUG02 11
2 17JUN04 29AUG02 22
As you can see, the variable count is correct for every value of b_date
except 17JUN04. For 17JUN04 the value should be 11.
Am I using the wrong approach? Can someone please help?
data in ;
input @1 id 1. +1 a_date MMDDYY10. +1 b_date MMDDYY10.;
datalines;
2,07/03/1995,05/07/2002
2,02/14/1996,12/06/2002
2,02/29/1996,06/07/2002
2,05/30/1997,12/11/2002
2,07/28/1999,11/26/2002
2,08/17/2000,06/17/2004
2,11/01/2000,12/04/2002
2,12/13/2000,12/13/2002
2,09/06/2001,06/17/2004
2,06/07/2002,11/29/2002
2,08/29/2002,01/02/2002
;
proc sql noprint feedback;
create table _in as
select
A.id ,
a.b_date,
b.a_date,
case
when b.a_date <= a.b_date
then 1
else .
end
as flag
from in as a, in as b
where a.id=b.id and
calculated flag
order by a.id , a.b_date ;
create table _in as
select
id,
b_date ,
a_date ,
sum(flag)
as count
from _in
group by id , b_Date ;
quit ;
data out ;
set _in ;
by id b_Date ;
if first.b_date ;
format a_Date b_Date date7.;
run;
proc print data = out noobs;
run ;
LEGAL NOTICE
Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this E-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents of this E-mail or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately.
| 
