Date:         Thu, 16 Apr 2009 11:14:50 -0400
Reply-To:     Akshaya <akshaya.nathilvar@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Akshaya <akshaya.nathilvar@GMAIL.COM>
Subject:      Re: Update statement based on 2 datasets
Comments: To: karma <dorjetarap@googlemail.com>
In-Reply-To:  <d6a0d8f10904160742n421cbe5bsd91d4ea703290cfd@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

How about using Sounds like operator?
Proc sql;
    select name, keyword as name_check
        from a left join b
            on scan(a.name,1)=*keyword
                order by 1;
Quit;

On Thu, Apr 16, 2009 at 10:42 AM, karma <dorjetarap@googlemail.com> wrote:

> As you discovered, you can't use like with variables, however you can
> use the eqt keyword in proc sql to mimic the SAS colon modifier =:
>
> data a ;
>   input name & $30. ;
>   length name_check $8 ;
> cards ;
> Mark J
> John Callahan
> Robert Ferguson
> Jennifer Miller
> ;
> data b;
>   input keyword $ ;
>   cards ;
> Robert
> John
> ;
> proc sql ;
>   update a set name_check=(select keyword from b where trim(keyword)
> eqt name) ;
>   quit ;
>
>
> 2009/4/16  <bagayat.harish@gmail.com>:
> > Hi All,
> >
> > I'm trying to update a dataset based on a column in other dataset. It
> > works similar to searching words in a column. Below the sample data:
> > Dataset: A
> > Name                       Name_check
> > Mark J
> > John Callahan
> > Robert Ferguson
> > Jennifer Miller
> >
> > Dataset: B
> > keywords
> > Robert
> > John
> >
> > Here I need to update Column Name_check in Dataset A if the name
> > matches (LIKE statement) with the keywords column in Dataset B.
> >
> > Output should be:
> > Name                       Name_check
> > Mark J
> > John Callahan          John
> > Robert Ferguson     Mark
> > Jennifer Miller
> > I could work this out in access. however, not able follow a similar
> > proc sql statement to update the data.
> >
> > Could anyone help me out on this query?
> >
> > Thanks for the support in advance.
> > HB
> >
>



--
AkshayA!