|
thank you Sig,
for the kind reference and welcome
May I ask if the sql style in your response below,
on this thread is typical of slq-heads ?
As you point out, below we see an sql contrib
from ds "die-hard" PD. There is a very noticeable
difference in syntax layouts. That I favour the PD-style
may point to some ds-preference of mine.
Is this style-disparity representative of some difference
between the instincts of a "sql-head" compared with
a "ds-head" , or something to do with sql ???
.....( i.e. is the personal choice, tool related )
And stepping away from personal issues,,,,
What are the performance difference (if any) resulting
from the two layers of your "view" based approach
compared with PD's single statement which only
employs " distinct " at the final stages...... ???
Kind Regards
Peter Crawford
...who has a great regard for ( sas provides a choice )
Datum: 29/05/2002 22:30
An: SAS-L@LISTSERV.UGA.EDU
Antwort an: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Betreff: Re: merge multiple observations into a single observation w/o ove rinflating matches
Nachrichtentext:
I have heard that even the SAS Masters have to struggle with problems of
coalescing data records! Even so, if you can declare a solution as a logical
statement, the SAS SQL genie can implement it.
Bet yo
> snips................
proc sql;
Create vie ACT68vw as select distinct ID,partresp,evtype,actcode,finalact
from A
where actcode=68
;
Create view ACT64vw as select distinct ID,parttrig,evtype,actcode from A
where actcode=64
;
Create table Answer as
select t1.*,t2.parttrig,t2.evtype as evtype64,t2.actcode as actcode64
from ACT68vw as t1 left join ACT64vw as t2
on t1.ID=t2.ID and t1.partresp=t2.parttrig and t1.evtype=t2.evtype
;
quit;
Datum: 29/05/2002 22:58
An: SAS-L@LISTSERV.UGA.EDU
Antwort an: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Betreff: Re: merge multiple observations into a single observation w/o ove rinf lating matches
Nachrichtentext:
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
> .
--
Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
| 
