Multiple values but only one calculation result

Reply
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,

Thanks in advance.

Trusted Advisor
Posts: 1,932

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

Trusted Advisor
Posts: 1,137

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,

jagadish

Thanks,
Jag
Respected Advisor
Posts: 4,173

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;

Ask a Question
Discussion stats
  • 3 replies
  • 220 views
  • 2 likes
  • 4 in conversation