Date: Fri, 25 Feb 2005 00:21:41 -0500
Reply-To: Krishnan Viswanathan <krisviswanathan@gmail.com>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Krishnan Viswanathan <krisviswanathan@GMAIL.COM>
Subject: Comparing Dates across multiple SAS datasets
Content-Type: text/plain; charset=US-ASCII
I would like to do the following in SAS. For each HHID VEHID combination,
I want to put a flag in the trip file based on the mwstarttime (from
the TRIP file) and
midwestdatetime (from the CARCHIPEVENT FILE) file for the following
conditions.
1)Flag1 indicating that the CarChip was connected for that entire day
2) Flag2 indicating that the CarChip was not connected at all that day
3) Flag3 indicating that a CarChip was connected at the beginning of the day
and at the end of the day, but was disconnected at some point during the
day
4) Flag4 indicating that a CarChip was disconnected at the beginning of the
day and connected at the end of the day
5) Flag5 indicating that a CarChip was connected at the beginning of the day
and disconnected at the end of the day
For eg, from March 7 to Apr 9 the 1st flag will be show a 1. On march 6
the 2nd flag will show a 1 and so on. Due to copying from excel text
file the time is not showing up.
INPUT FILES :
CARCHIPEVENT FILE [~ 5000 Records]
HHID VEHID MIDWESTDATETIME CHIPID EVENT
1007 1 3/6/2004 695 Connected
1007 1 4/10/2004 695 Disconnected
1007 1 4/10/2004 772 Connected
1007 1 5/8/2004 772 Disconnected
1007 1 5/8/2004 655 Connected
1007 1 6/9/2004 655 Disconnected
1007 1 8/11/2004 650 Connected
1007 1 10/10/2004 650 Disconnected
1009 1 3/16/2004 690 Connected
1009 1 5/1/2004 690 Disconnected
1009 1 5/1/2004 556 Connected
1009 1 6/3/2004 556 Disconnect record written after last trip
1009 1 6/3/2004 617 Connected
1009 1 7/19/2004 617 Disconnected
TRIP FILE [~ 10000 Records]
HHID VEHID TRIPID MWSTARTTIME MWENDTIME DURATION DISTANCE
1007 1 1 3/6/2004 3/6/2004 0.2 0 0
1007 1 2 3/7/2004 3/7/2004 11.5 4.7 0
1007 1 3 3/7/2004 3/7/2004 8.6 3 0
1007 1 4 3/7/2004 3/7/2004 4.4 1.6 0
1007 1 5 3/7/2004 3/7/2004 1.1 0 0
1007 1 6 3/7/2004 3/7/2004 11.2 1.5 0
1007 1 7 3/7/2004 3/7/2004 3.5 0.8 0
1007 1 8 3/8/2004 3/8/2004 22.6 5 0
1007 1 9 3/8/2004 3/8/2004 4.3 0.8 0
1007 1 10 3/8/2004 3/8/2004 0.4 0 0
1007 1 11 3/8/2004 3/8/2004 4.1 0.8 0
1007 1 12 3/9/2004 3/9/2004 3.8 0.9 0
1007 1 13 3/9/2004 3/9/2004 22.8 13.7 0
1007 1 14 3/9/2004 3/9/2004 0.4 0 0
1007 1 15 3/9/2004 3/9/2004 16.2 12.7 0
1007 1 16 3/9/2004 3/9/2004 3.9 0.9 0
1007 1 17 3/10/2004 3/10/2004 4 0.8 0
1007 1 18 3/10/2004 3/10/2004 3.1 0.8 0
1007 1 19 3/11/2004 3/11/2004 9.6 4.5 0
1007 1 20 3/11/2004 3/11/2004 2.9 0.5 0
1007 1 21 3/11/2004 3/11/2004 2.7 0.5 0
1007 1 22 3/12/2004 3/12/2004 14.1 8 0
1007 1 23 3/12/2004 3/12/2004 16.8 6.5 0
1007 1 24 3/12/2004 3/12/2004 4.9 1.9 0
1007 1 25 3/12/2004 3/12/2004 3.6 1.2 0
1007 1 26 3/12/2004 3/12/2004 36.3 18.6 0
1007 1 27 3/12/2004 3/12/2004 25.5 17.3 0
1007 1 28 3/12/2004 3/12/2004 4.6 1.2 0
1007 1 29 3/13/2004 3/13/2004 37.8 33.9 0
DESIRED OUTPUT :
HHID VEHID TRIPID MWSTARTTIME MWENDTIME DURATION DISTANCE FLAG1 FLAG2 FLAG3 FLAG4 FLAG5
1007 1 1 3/6/2004 3/6/2004 0.2 0 0 0 0 1 0
1007 1 2 3/7/2004 3/7/2004 11.5 4.7 1 0 0 0 0
1007 1 3 3/7/2004 3/7/2004 8.6 3 1 0 0 0 0
1007 1 157 4/10/2004 4/10/2004 3.6 1.3 0 0 1 0 0
1007 1 158 4/10/2004 4/10/2004 3.5 0.4 0 0 1 0 0
I have tried the following code :
data ccevents;
set mbuf.ccevents;
by hhid vehid ccdate;
lastHHID=lag(hhid);
lastVEHID=lag(vehid);
lastDttm=lag(MIDWESTDATETIME);
if first.vehid then do;
lastDttm=.;
end;
if lastHHID=hhid & lastVehid=vehid then do;
span1=MIDWESTDATETIME - lastdttm ;
end;
format lastDttm date9. ;
run;
And then running a comparison between MWSTARTTIME and span1.
But because I can merge only by HHID & VEHID there are first & last
dates and times are repeated and screws up the results. Is there a
way to compare the two datasets without merging where if the date in
the TRIP file lies between two dates in different rows in the CARCHIP
file I can create a flag?
TIA,
Krishnan
--
Krishnan Viswanathan
1555-2 Coombs Dr
Tallahassee, FL 32308
http://www.aidindia.org
http://www.aidboston.org
