topic Combinations in SAS Programming

Combinations

SASPhile — Thu, 29 Mar 2012 18:33:49 GMT

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.

Combinations

Ksharp — Fri, 30 Mar 2012 05:49:20 GMT

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

Combinations

SASPhile — Tue, 03 Apr 2012 16:46:24 GMT

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

Combinations

MartyS — Tue, 03 Apr 2012 17:01:17 GMT

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