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.
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.
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.
What if both DAD_01 and DAD_10 contains values?
I would want to sum the values from DAD_01 and DAD_10
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.