BookmarkSubscribeRSS Feed
brm
Calcite | Level 5 brm
Calcite | Level 5

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.

9 REPLIES 9
Reeza
Super User

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

Ksharp
Super User
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
Calcite | Level 5 brm
Calcite | Level 5

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
Calcite | Level 5 brm
Calcite | Level 5

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

Ksharp
Super User

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
Super User

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
Calcite | Level 5 brm
Calcite | Level 5

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
Fluorite | Level 6

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
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1200 views
  • 0 likes
  • 5 in conversation