LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (May 2002, week 5)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Wed, 29 May 2002 17:49:16 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: merge multiple observations into a single observation w/o ove
              rinf lating matches
Comments: To: "Dorfman, Paul" <Paul.Dorfman@BCBSFL.COM>
Content-Type: text/plain; charset="iso-8859-1"

Has anyone else noticed that a number of hard-core datastep programmers are now crossing over and posting SQL solutions? First Peter Crawford, then Hash-man .... Who next? Dr. John?

I have only one thing to say about these incursions into what I used to consider my own little enclave. Welcome!

Sig

-----Original Message----- From: Dorfman, Paul [mailto:Paul.Dorfman@BCBSFL.COM] Sent: Wednesday, May 29, 2002 4:31 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: merge multiple observations into a single observation w/o ove rinf lating matches

Shannon,

If you do not mind a shot from an SQL-impared (suppose your input data set is called A):

Proc SQL ; Create table Match as Select distinct x.id , y.parttrig , x.partresp , x.evtype , x.actcode , x.finalact From A (where=(actcode=68)) x left join A (where=(actcode=64)) y on x.id = y.id and x.evtype = y.evtype and x.partresp = y.parttrig Order by id , evtype ; Quit ;

Kind regards, ================== Paul M. Dorfman Jacksonville, FL ==================

> From: Shannon [mailto:swheatma@FJC.GOV] > > Greetings- I am wondering if anyone has any thoughts about how to > merge multiple observations into a single observation without > overinflating the true number of matches. Below is an example of a > dataset in its current form (DATASET A) and the desired result > (DATASET B). > > DATASET A: > > ID PARTTRIG PARTRESP EVTYPE ACTCODE > FINALACT > > 304 T1 DLSM 64 > . > 304 JD FPRO 64 > . > 304 T1 DLSM 68 > 1 > 304 JD FPRO 68 > 2 > 1832 D1 FPRO 64 > . > 1832 D1 FPRO 64 > . > 1832 D1 FPRO 68 > . > 1832 D1 FPRO 68 > 2 > 1832 P2 SMJD 68 > 1 > > > > The goal is to match the records with ACTCODE=68 to the corresponding > records with ACTCODE=64 by unique ID group, EVTYPE and where > PARTTRIG=PARTRESP. The thing to avoid is multiple matches that result > when records with similar matching criteria are merged using a MERGE > statement. I think the key is to use SQL and perhaps set up a flag > that prevents a record that is matched once from being matched a > second time with each iteration until all possible match combinations > are attained. An example of the desired result would be: > > DATASET B: > > ID PARTTRIG PARTRESP EVTYPE ACTCODE > FINALACT > > 304 T1 T1 DLSM 68 > 1 > 304 JD JD FPRO 68 > 2 > 1832 D1 D1 FPRO 68 > . > 1832 D1 D1 FPRO 68 > 2 > 1832 P2 SMJD 68 > 1 >

Blue Cross Blue Shield of Florida, Inc., and its subsidiary and affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.


Back to: Top of message | Previous page | Main SAS-L page