Date: Tue, 19 Jun 2007 00:27:45 -0400
Reply-To: Ya Huang <ya.huang@AMYLIN.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ya Huang <ya.huang@AMYLIN.COM>
Subject: Re: How to search list of particular text values into free text
variable in Main SAS table
Content-Type: text/plain; charset=ISO-8859-1
This one may not be efficient when look up table are big,
but at least it saves your typing:
proc sql;
create table xx as
select distinct a.srno,a.order_details,
max(case when a.order_details contains trim(b.types) then b.types else ''
end) as btype
from large_table a, smalltable b
group by a.srno
order by a.srno
;
proc print;
run;
Srno Order_details btype
1 The order of Mangos .. Mangos
10 Retured all Potatoes .. Potatoes
2 The returned order .. Mangos
3 Order for the Oranges .. Oranges
4 new container for the Onions .. Onions
5 Boxes of Grapes received .. Grapes
6 Almonds boxes received .. Almonds
7 Dry fruits received on last .. Dry fruits
8 Spinach were tossed out .. Spinach
9 Begs of Potatoes received .. Potatoes
On Mon, 18 Jun 2007 13:29:55 -0400, SUBSCRIBE SAS-Nim Pat
<Npp1129@GMAIL.COM> wrote:
>On Mon, 18 Jun 2007 12:02:45 -0400, SUBSCRIBE SAS-Nim Pat
><Npp1129@GMAIL.COM> wrote:
>
>>Hi All,
>>Please if anyone has idea about how to search particular list of text
>>values from one small table to free text variable in large dataset and
>>when finds the value, update the large dataset.
>>I have small lookup table (around 120 records) for example:
>>Category Types;
>>
>>Type_1 Mangos,
>>Type_1 Pineapples,
>>Type_2 Onions,
>>Type_2 Tomatoes,
>>…
>>
>>Type_n typeN.
>>
>>
>>Now I need to search each particular text values (variable-Types) from the
>>above small table into my large dataset which has free text variable
>>called “Order_details”.
>>For example:
>>Order_details: The last order was for the Mangos on Nov 11 2006.
>>
>>
>>I’m using following code but it’s not efficient:
>>PROC SQL;
>>Create table new_Main_Table AS
>>SELECT *,
>>Case
>>when Order_details contains (‘Mangos’) then “Type_1”
>>when Order_details contains (‘Pineapples’) then “Type_1”
>>when Order_details contains (‘Onions’) then “Type_2”
>>when Order_details contains (‘Tomatoes’) then “Type_2”
>>.
>>.
>>.
>>when Order_details contains (‘typeN’) then “Type_n”
>>Else “ “
>>End As order_Types
>>From Main_table ;
>>Quit;
>>Run;
>>
>>Thanks for your time and help,
>>I really appreciate your effort,
>>Nim
>
>Guys Sorry for the Inconveniencies. I'm attaching sample table.
>my Small lookup table:
>Data SmallTAble ;
>Length Category Types $ 25 ;
>Input Category Types;
>Cards ;
>Type_1 Mangos
>Type_1 Oranges
>Type_1 Pineapples
>Type_1 Peaches
>Type_1 Pears
>Type_1 Kiwis
>Type_1 Grapes
>Type_2 Onions
>Type_2 Tomatoes
>Type_2 Carrots
>Type_2 Carrots
>Type_2 Celery
>Type_2 Potatoes
>Type_2 Spinach
>Type_3 Almonds
>Type_3 Cashews
>Type_4 Dry fruits
>;
>
>And My LARGE Table with Free Text variable :
>
>Data Large_table ;
>Length Srno $4 Order_details $400 ;
>Input Srno Order_details ;
>Cards ;
>
>1 The order of Mangos dispatched on Nov. 11 2006
>2 The returned order received from the customer for Mangos was on
>Dec. 12 2006
>3 Order for the Oranges dispatched on Nov. 26 2006
>4 new container for the Onions received from NJ on Dec. 1 2006
>5 Boxes of Grapes received from FL on Dec 2007
>6 Almonds boxes received from IL on Nov. 2006
>7 Dry fruits received on last month were dispatched to NJ on dec.
>2006
>8 Spinach were tossed out from the store on Nov. 2006
>9 Begs of Potatoes received from FL on Nov. 2006
>10 Retured all Potatoes to company on Dec 2006
>
>Thanks,
>Nim Pat
| 
