BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8


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.

3 REPLIES 3
Ksharp
Super User

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
Quartz | Level 8

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

MartyS
Calcite | Level 5

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

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
  • 3 replies
  • 999 views
  • 0 likes
  • 3 in conversation