to create a new column

[brm]

Hi,

can someone suggest me how to do this one.

   I have main table with the products.

  account id  product
  xyz      1  00123
  xyz      2  145
  abc      3  314
  abc      2  00678
  def      4  4567
  efg      5  00123

  i have three other tables...in one table i have product codes for 'xyz' account

   product  productcode
   123       avt
   145       but
   156       cer
  second table have product codes for 'abc' account.
   product  productcode
   314       tes
   418       mos

  thrid table have product codes for 'all other' accounts.
   product  productcode
   4567     nji
   123      kli
   156      utr

  I have to get product codes for main table by checking these three tables.
  if product is not found in any of the table leave that coloum as empty.

 
  Final table:

  account id  product  productcode
  xyz      1  00123     avt
  xyz      2  145       but
  abc      3  314       tes
  abc      2  00678
  def      4  4567      nji
  efg      5  00123     kli

  i'm doing this using left outer join and union each result....but final results are
  not getting properly....

Thanks,
vk.

[Reeza]

Post the code and perhaps we can help debug rather than start from scratch?

[Ksharp]

data main;
input account $ id  product$ ;
cards;
  xyz      1  00123
  xyz      2  145
  abc      3  314
  abc      2  00678
  def      4  4567
  efg      5  00123
;run;
data xyz;
input product $ productcode $;
cards;
   123       avt
   145       but
   156       cer
;run;
data abc;
input product $ productcode $;
cards;
   314       tes
   418       mos
;run;
data others;
input product $ productcode $;
cards;
   4567     nji
   123      kli
   156      utr 
;run;
data temp;
 set xyz abc others indsname=_dsn;
 dsn=scan(_dsn,2);
run;
data main;
 set main;
 if account='xyz' then dsn=upcase(account);
  else if account='abc' then dsn=upcase(account);
   else dsn='OTHERS';
run;
proc sql;
 create table want as
  select a.account,a.id,a.product,b.productcode
   from main as a left join temp as b
    on a.dsn=b.dsn and a.product contains strip(b.product)
    ;
quit;

Ksharp

[brm]

Hi,

     I'm trying the above code.....need to change it to display proper account names other than xyz and abc.

     could you please suggest me how to do that.

Thanks,

Bhavani.

[brm]

And also account names and lookup table names won't match in real scenario.Because account names having spaces ex:"sdi xyz"

[Ksharp]

But How can we identify the observations in the main table for these account names with the following several datasets. Namely how did I know which account dataset has which some product in main dataset?

Ksharp

[Ksharp]

You can use contains operator to accout names Such as:

   a.dsn contains strip(b.dsn) and a.product contains strip(b.product)

Ksharp

[brm]

ksharp,

         same product name will exisist in more than one table.....so already in the requirement they r specifying that for this account look in table1 etc.

Thanks,

vk sk

[dhana]

Hi,

Please find attached the solution. To keep it simple and understanding I didn't write complex join. If you want to simplify the query you can still make it.

Thanks

Dhanasekaran R

P.S: Can some one tell me how to paste the code in the Editor Window...!  Since I am not able to paste the code here, I am attaching...! :smileyconfused:

The CTRL+V is not working in the Editor Window....! :smileycry:

[Doc_Duke]

Dhana,

Take your Ctrl+V comment to the getting started forum so the admin will see it.  Include your OS and Browser.  There are definitely some "features" that need adjusting in this tool.

(It works for me with Firefox 4 on a Win XP SP 3 PC.)

Doc