DATA Step, Macro, Functions and more

Help to join and re-label variables

Reply
Contributor
Posts: 36

Help to join and re-label variables

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.

Super User
Super User
Posts: 7,942

Re: Help to join and re-label variables

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.

Contributor
Posts: 36

Re: Help to join and re-label variables

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
Contributor
Posts: 36

Re: Help to join and re-label variables

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.
Super User
Super User
Posts: 7,942

Re: Help to join and re-label variables

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.

PROC Star
Posts: 733

Re: Help to join and re-label variables

What if both DAD_01 and DAD_10 contains values?

Contributor
Posts: 36

Re: Help to join and re-label variables

I would want to sum the values from DAD_01 and DAD_10

Ask a Question
Discussion stats
  • 6 replies
  • 125 views
  • 0 likes
  • 3 in conversation