Date: Thu, 15 May 2003 15:53:50 -0400
Reply-To: Ian Whitlock <WHITLOI1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ian Whitlock <WHITLOI1@WESTAT.COM>
Subject: FW: Array to Re-arrange Data
Content-Type: text/plain
Rish,
You cannot have records ordered by date and time within appappid AND have
statid on the next record equal to statnextid on the previous one (unless
the data supports it). I assumed you meant the records should be in
APPAPPID order and within that begin with STATID=-339818 and also meet the
nextid condition. With that understanding here is the code.
data w ;
input
APPAPPID STATID STATNEXTID STATDATE :mmddyy8. temp &$11. ;
STATTIME = input (compress(temp),time11.) ;
format statdate date9. stattime time. ;
cards ;
355843 -181415 -181418 2/11/02 4:02:14 AM
355843 -339495 -339496 2/11/02 12:28:07 AM
355843 -181418 -181416 2/11/02 4:02:14 AM
355843 -339498 -342454 2/11/02 12:28:11 AM
355843 -339497 -339498 2/11/02 12:28:11 AM
355843 -339499 -339500 2/11/02 12:28:16 AM
355843 -339818 -339495 2/11/02 12:28:07 AM
355843 -339500 -181415 2/11/02 12:28:17 AM
355843 -342454 -339499 2/11/02 12:28:11 AM
355843 -339496 -339497 2/11/02 12:28:11 AM
;
proc sort data = w ;
by appappid statdate stattime ;
run ;
data init ( keep = appappid ) ;
set w ;
by appappid ;
if first.appappid ;
run ;
proc datasets lib = work nolist ;
modify w ;
index create key = ( appappid statid ) / unique ;
run ;
quit ;
data q ( drop = done ) ;
set init ;
STATID=-339818 ;
set w key = key ;
link check ;
do until ( done ) ;
set w key = key ;
link check ;
end ;
return ;
check :
if _iorc_ = 0 then
do ;
output ;
statid = statnextid ;
end ;
else
do ;
done = 1 ;
_error_ = 0 ;
end ;
return ;
run ;
If you are unsatisfied with the execution time then consider a hash array to
hold a pointer to the record given the STATID for a given APPAPPID. If you
don't understand how to do this search for Paul Dorfman or use SAS version
9.
IanWhitlock@westat.com
-----Original Message-----
From: Rish [mailto:Rishi.Sood@CIBC.COM]
Sent: Thursday, May 15, 2003 2:16 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Array to Re-arrange Data
Hi Everyone,
I was looking to arrange/sort data in a particular way. The primary sort
keys are appappid, date and time and then after that sorted in a backwards S
type of pattern using the STATNEXTID in the above record to select the
corresponding record. Here are two example data sets:
BEFORE
APPAPPID STATID STATNEXTID STATDATE STATTIME
355843 -181415 -181418 2/11/02 4:02:14 AM
355843 -339495 -339496 2/11/02 12:28:07 AM
355843 -181418 -181416 2/11/02 4:02:14 AM
355843 -339498 -342454 2/11/02 12:28:11 AM
355843 -339497 -339498 2/11/02 12:28:11 AM
355843 -339499 -339500 2/11/02 12:28:16 AM
355843 -339818 -339495 2/11/02 12:28:07 AM
355843 -339500 -181415 2/11/02 12:28:17 AM
355843 -342454 -339499 2/11/02 12:28:11 AM
355843 -339496 -339497 2/11/02 12:28:11 AM
AFTER
APPAPPID STATID STATNEXTID STATDATE STATTIME
355843 -339818 -339495 2/11/02 12:28:07 AM
355843 -339495 -339496 2/11/02 12:28:07 AM
355843 -339496 -339497 2/11/02 12:28:11 AM
355843 -339497 -339498 2/11/02 12:28:11 AM
355843 -339498 -342454 2/11/02 12:28:11 AM
355843 -342454 -339499 2/11/02 12:28:11 AM
355843 -339499 -339500 2/11/02 12:28:16 AM
355843 -339500 -181415 2/11/02 12:28:17 AM
355843 -181415 -181418 2/11/02 4:02:14 AM
355843 -181418 -181416 2/11/02 4:02:14 AM
Within each APPAPPID the first record will always be STATID=-339818. Then
from there I need to try and get a domino effect downwards. Where the
STATNEXTID of the above record matches the STATID of the record below it.
Keeping in mind that STATDATE AND STATTIME are sorted in descending order.
To Recap:
1) Data initially sorted by APPAPPID, STATDATE and STATTIME
2) then within each APPAPPID the first record is always STATID=-339818
3) and after that STATNEXTID=STATID of the record below.
I don't believe the lag function can be used for this because within a group
of similar times STATNEXTID and STATID could be many records apart.
Thank you in advance for your help and please ask me any questions you might
have. Regards, Rishi
