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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.