06-30-2011 06:00 PM
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
second table have product codes for 'abc' account.
thrid table have product codes for 'all other' accounts.
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.
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....
06-30-2011 11:27 PM
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;
07-01-2011 12:57 PM
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.
07-03-2011 10:26 PM
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?
07-05-2011 07:27 PM
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.
07-06-2011 04:14 AM
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.
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:
07-06-2011 09:53 AM
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.)