Contributor
Posts: 29

# Multiple values but only one calculation result

Hi,

I am stuck in a problem, which might be due to data manipulation. I have a table with contracts and many options which I might need or might not to calculate a field:

option table:

Contract    Option    Value

0000001        1           0

0000001        2           0

0000001        3           0

0000002        1           1

0000002        3           1

0000003        1           0

0000003        2           1

0000003        3           0

Data table (amt is needed for calc field):

Contract        Amt

0000001        100

0000002        20

0000003        300

Calc table (using Amt in Data table and adjusting if option values):

Contract        Calc (Amt - Value in opt table)

0000001        100

0000002        18

0000003         299

Any ideas how to approach this problem? The multiplicity is throwing me off,

Posts: 3,029

## Re: Multiple values but only one calculation result

Step 1: Sum the values in the option table, by contract

Step 2: Merge the sums with the data table, by contract, and subtract sum of the values from amount

--
Paige Miller
Posts: 1,147

## Re: Multiple values but only one calculation result

somthing like the below code will work for you, this is in accordance with the PaigeMiller suggestion

data have;

input Contract  Option  Value;

format contract z7.;

cards;

0000001        1           0

0000001        2           0

0000001        3           0

0000002        1           1

0000002        3           1

0000003        1           0

0000003        2           1

0000003        3           0

;

run;

proc sort data=have;

by contract;

run;

data have_;

set have;

retain sum;

by contract;

if first.contract then sum=value;

else sum+value;

if last.contract;

run;

data amt;

input Contract Amt;

format contract z7.;

cards;

0000001        100

0000002        20

0000003        300

;

proc sort data=amt;

by contract;

run;

data want;

merge have_(in=a) amt(in=b);

by contract;

if a;

calc=Amt-sum;

run;

proc print;

run;

Thanks,

Thanks,
Jag
Posts: 4,736

## Re: Multiple values but only one calculation result

Using a SQL

data Options;
input Contract Option Value;
format contract z7.;
cards;
0000001 1 0
0000001 2 0
0000001 3 0
0000002 1 1
0000002 3 1
0000003 1 0
0000003 2 1
0000003 3 0
;
run;

data Amt;
input Contract Amt;
format contract z7.;
cards;
0000001 100
0000002 20
0000003 300
0000004 500
;
run;

proc sql;
create table Calc as
select A.contract, A.amt, A.amt - O.sum_value as calc
from
Amt A left join
(select contract, sum(value) as sum_value from Options group by contract) O
on A.contract=O.contract
;
quit;

Discussion stats
• 3 replies
• 241 views
• 2 likes
• 4 in conversation