Date: Thu, 6 Feb 2003 16:14:23 -0500
Reply-To: "Karl K." <karlstudboy@HOTMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Karl K." <karlstudboy@HOTMAIL.COM>
Subject: Re: Merging datasets ...
Content-Type: text/plain; charset=ISO-8859-1
If the master lookup table isn't huge, there's a sql solution that, if
nothing else, is elegant:
input id xfrom xto;
1 1 3
2 4 23
3 26 500
from one left join main_lookup
on x between xfrom and xto
order by 1
On Thu, 6 Feb 2003 21:58:10 +0100, Peter Crawford <peter.crawford@DB.COM>
>I don't think this is suitable for a merge, because
>dataset2 would be joined on a variable-pair range,
>and not a single variable
>It could be an interleaved set when the datasets are in
>"number" and "from" order
>data trial( keep = id number ) ;
> set dataset2( rename=( from = number)
> dataset1( in= data1 ) ;
> by number;
> * obviously set provides data from only one dataset
> at a time. When it is dataset2, keep the range.
> When it is dataset1, compare with the latest range;
> if not data1 then
> from2 = number ;
> *store interval range. If no to value, then store to =from ;
> if not missing(to) then to2=to;
> else to2=number;
> id2 = id;
> if from2 <= number <= to2 then
> then id = id2 ;
> else id = . ;
> output ;
> retain from2 to2 id2; * remember to keep values from dataet2;
>I have had to write this without pre-judging whether "number",
>"to" and "from" are character or numeric. They only have to be
>in "from" and "number" order.
>Beware, this is untested..
>Datum: 06/02/2003 21:23
>Antwort an: Ramesh Makkena <makkena@XOMA.COM>
>Betreff: Re: Merging datasets ...
>If your look up table values are few then I agree with Roger. If you
happen to have thousands of lookup values then You obviously have to leave
>From: Roger Lustig [mailto:trovato@BELLATLANTIC.NET]
>Sent: Thursday, February 06, 2003 11:36 AM
>Subject: Re: Merging datasets ...
>No merging required! What you need is a format.
>Ultimately, you'll just write a short program like this:
>*** Or, if you want ID to be numeric:
>Now, how to make the MYFORMAT format?
>For this you need the following:
>proc format cntlin=<data set name>;
>The data set in question can be generated from the one that contains the
>FROM and TO variables now. For that, you'll have to read the manual,
>though--under PROC FORMAT.
>Johny Bravo wrote:
>> Hi !
>> I have a following problem.
>> I have two datasets.
>> First dataset contains numbers, e.g.:
>> And second dataset that contains intervals, e.g.:
>> id from to
>> 1 1 3
>> 2 4 23
>> 3 26 500
>> ... .... ....
>> I want to classify all numbers from first dataset
>> to intervals in the second. I want to have on output
>> dataset that contains number and an ID of particular
>> interval, e.g.:
>> number ID
>> 23 2
>> 24 null
>> 435 3
>> 45 3
>> ... ...
>> ( BTW intervals in second set are separate)
>> I am wondering how to do it with data step and maybe "retain".
>> Maybe If-clause is nessesary, i don't know :(
>> The main problem is how to join datasets with diffrent number
>> of observations.
>> Thanks in advance for advice,
>> Best regards,
>> Johny Bravo ;)
>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.