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:

data one;
 input x;
 datalines;
23
24
435
45
;
data main_lookup;
 input id xfrom xto;
 datalines;
1  1   3
2  4  23
3 26 500
;
proc sql;
 select x,id
  from one left join main_lookup
       on x between xfrom and xto
  order by 1
 ;

Karl

On Thu, 6 Feb 2003 21:58:10 +0100, Peter Crawford <peter.crawford@DB.COM>
wrote:

>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
>   do;
>     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;
>   end;
> else
>   do;
>      if from2  <= number <= to2 then
>        then id = id2 ;
>        else id = . ;
>      output ;
>   end;
> else
> retain from2 to2 id2; * remember to keep values from dataet2;
>run;
>
>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..
>Good Luck
>Peter Crawford
>
>
>
>Datum:         06/02/2003 21:23
>An:            SAS-L@LISTSERV.UGA.EDU
>
>
>
>Antwort an:    Ramesh Makkena <makkena@XOMA.COM>
>
>Betreff:       Re: Merging datasets ...
>Nachrichtentext:
>
>Hi Johny,
>
>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
the formats.
>
>Thanks,
>Makkena
>
>-----Original Message-----
>From: Roger Lustig [mailto:trovato@BELLATLANTIC.NET]
>Sent: Thursday, February 06, 2003 11:36 AM
>To: SAS-L@LISTSERV.VT.EDU
>Subject: Re: Merging datasets ...
>
>
>Dear Johny:
>
>No merging required!  What you need is a format.
>
>Ultimately, you'll just write a short program like this:
>
>data new_num;
>set numbers;
>id=put(number,myformat.);
>*** Or, if you want ID to be numeric:
>    id=input(put(number,myformat.),6.);
>run;
>
>Now, how to make the MYFORMAT format?
>
>For this you need the following:
>
>proc format cntlin=<data set name>;
>run;
>
>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.
>
>Good luck!
>
>Roger
>
>Johny Bravo wrote:
>> Hi !
>>
>> I have a following problem.
>> I have two datasets.
>> First dataset contains numbers, e.g.:
>> number
>> 23
>> 24
>> 435
>> 45
>> ...
>> 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.