topic to create a new column in SAS Programming

to create a new column

brm — Thu, 30 Jun 2011 22:00:10 GMT

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.

to create a new column

Reeza — Thu, 30 Jun 2011 23:13:21 GMT

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

to create a new column

Ksharp — Fri, 01 Jul 2011 03:27:14 GMT

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

to create a new column

brm — Fri, 01 Jul 2011 16:57:05 GMT

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.

to create a new column

brm — Fri, 01 Jul 2011 18:32:42 GMT

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

to create a new column

Ksharp — Mon, 04 Jul 2011 02:26:59 GMT

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

to create a new column

Ksharp — Mon, 04 Jul 2011 02:35:55 GMT

You can use contains operator to accout names Such as:

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

Ksharp

to create a new column

brm — Tue, 05 Jul 2011 23:27:34 GMT

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

Re: to create a new column

dhana — Wed, 06 Jul 2011 08:14:26 GMT

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:

to create a new column

Doc_Duke — Wed, 06 Jul 2011 13:53:12 GMT

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