topic Re: Help to join and re-label variables in SAS Programming

Help to join and re-label variables

Havi — Wed, 22 Feb 2017 12:11:14 GMT

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.

Re: Help to join and re-label variables

RW9 — Wed, 22 Feb 2017 12:18:16 GMT

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.

Re: Help to join and re-label variables

PeterClemmensen — Wed, 22 Feb 2017 12:19:03 GMT

What if both DAD_01 and DAD_10 contains values?

Re: Help to join and re-label variables

Havi — Wed, 22 Feb 2017 12:24:23 GMT

I would want to sum the values from DAD_01 and DAD_10

Re: Help to join and re-label variables

Havi — Wed, 22 Feb 2017 12:26:47 GMT

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

Re: Help to join and re-label variables

Havi — Wed, 22 Feb 2017 12:34:07 GMT

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.

Re: Help to join and re-label variables

RW9 — Wed, 22 Feb 2017 12:39:48 GMT

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.