Help to join and re-label variables

[Havi]

Hi Guys 

Please help with the issue below:

I have table one: SAMPLE1

Date            Grade         Cost          DAD_01        DAD_03         DAD_10           DAD_20
20170101    1                 100           30                  70
20170101    2                 200                                                        125                   75

I have table two: REFERENCE1

ID           CATEGORY       NAME         UNIT
01           PAID                   XXX            R
03           UNPAID              YYY            X
10           PAID                   ZZZ             R
20           UNPAID              PPP            X

The desired output is to link the ID in table one to the reference data in table two:

The desired output:

Date       Grade        Cost       R_PAID       X_UNPAID
20170101 1              100        30               70
20170101 2              200       125              75

Thanks for the help.

[RW9]

Fist off, post test data in the form of a datastep.  Also, "The desired output is to link the ID in table one to the reference data in table two:" - how, there doesn't appear to be any logical link between them.  Also clarify why you want to do it as simply:

data want (keep=date grade cost r_paid x_unpaid);
  set have;
  array vars{*} dad_:;
  do i=1 to dim(vars{*});
    if vars{i} ne "" and r_paid="" then r_paid=vars{i};
    else if vars{i} ne "" and x_unpaid="" then x_unpaid=vars{i};
  end;
run;

Should get you the output, the second table is irrelevant.

[Havi]

Apologies it was not clear. The DAD_01 in table one links to the ID 01 in table two. So I would first have to extract the integers from DAD_01 to link it to ID 01

[Havi]

Ideally I want to sum all the values in table one with the ID properties from table two. Table two is important and acts as a reference table for the ID's to determine PAID/UNPAID and UNIT i.e R/X. I then want to concat the properties eg R_PAID with the values from the ID.

[RW9]

Ah, ok I see.  Then the logic would be something like:
1) Normalise sample1, something like:

proc transpose data=sample1 out=sample;
  by date grade cost;
  var dad:;
run;

2) Process that to remove DAD_from _name_.

3) Datastep sum up the values, or use summary/means

4) Merge other dataset.

5) Transpose back up.

If you post test data, in the form of a datastep, then I can try some code.

[PeterClemmensen]

What if both DAD_01 and DAD_10 contains values?

[Havi]

I would want to sum the values from DAD_01 and DAD_10