BookmarkSubscribeRSS Feed
Havi
Obsidian | Level 7

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.

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7
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
Obsidian | Level 7
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Tourmaline | Level 20

What if both DAD_01 and DAD_10 contains values?

Havi
Obsidian | Level 7

I would want to sum the values from DAD_01 and DAD_10

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