Combinations

[SASPhile]

I have a data like this

Product          Altid           HCC                                
Abc             12345           HCC10
Abc             12345           HCC20 
Abc             12345           HCC30
Abc             12345           HCC40               

ABC             23456           HCC10
ABC             23456           HCC20
ABC             23456           HCC30
ABC             23456           HCC40
ABC             23456           HCC50  

ABC             78912           HCC10
ABC             78912           HCC50
ABC             78912           HCC20
ABC             78912           HCC30

The combination table for possible hCC combinations are

Combination Table

HCC1      HCC2     HCC3           Result
HCC10     HCC20                     HCC900
HCC30     HCC40                     HCC900
HCC30     HCC40    HCC50      HCC901
HCC10     HCC30                     HCC902

The weights table for HCC is

weight table:
HCC           weight
HCC900       5
HCC901       6
HCC902       7

How to populate dataset like this:
Product           Altid            HCC_combo                  final_weight
Abc             12345           HCC900 HCC902                12
ABC             23456           HCC900 HCC901               11
ABC             78912           HCC900 HCC902               12

Altid 12345 has hcc_combo as HCC900 HCC902 because hcc10 and hcc20 is hcc900.also hcc10 and hcc30 is hcc900.we take only once hcc900.
also hcc10 and hcc30 is hcc902. sum of weights of hcc900 and hcc902 is 5+7 which is 12.This combination is from HCC combination table.

same for other altids too.

[Ksharp]

Product           Altid            HCC_combo                  final_weight

Abc             12345           HCC900 HCC902                12

ABC             23456           HCC900 HCC901               11

ABC             78912           HCC900 HCC902               12

I do not understand why it is not HCC900 HCC901 HCC902 .

What criterion are you using to make a HCC_combo variable ?

Ksharp

[SASPhile]

Well...I used a column wise computation than using an array and then merged the data to the original dataset.

[MartyS]

I am a SQL guy and new to SAS, but off the top of my head, the following SQL should get you very close:

SELECT p1.product, c.result

FROM combinations c, products p1, products p2

WHERE c.hcc1 = p1.hcc

AND c.hcc2 = p2.hcc

AND c.hcc3 IS NULL

AND p1.product = p2.product

UNION

SELECT p1.product, c.result

FROM combinations c, products p1, products p2, products p3

WHERE c.hcc1 = p1.hcc

AND c.hcc2 = p2.hcc

AND c.hcc3 = p3.hcc

AND p1.product = p2.product

AND p1.product = p3.product