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.
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
Well...I used a column wise computation than using an array and then merged the data to the original dataset.
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: