f



How to search list of particular text values into free text

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
0
npp1129 (10)
6/18/2007 4:02:45 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

3 Replies
650 Views

Similar Articles

[PageSpeed] 39

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
0
npp1129 (10)
6/18/2007 5:29:55 PM
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
0
ya.huang (1962)
6/19/2007 4:27:45 AM
Sorry, there was a minor error, b.types should be b.category:

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.category
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 ..          Type_1
 10   Retured all Potatoes..          Type_2
 2    The returned order ..           Type_1
 3    Order for the Oranges ..        Type_1
 4    new container for the Onions .. Type_2
 5    Boxes of Grapes received ..     Type_1
 6    Almonds boxes received ..       Type_3
 7    Dry fruits received on ..       Type_4
 8    Spinach were tossed out ..      Type_2
 9    Begs of Potatoes ..             Type_2

On Tue, 19 Jun 2007 00:27:45 -0400, Ya Huang <ya.huang@AMYLIN.COM> wrote:

>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
0
ya.huang (1962)
6/19/2007 4:32:15 AM
Reply: