CODEWITHSUNDEEP
sas
yjrtcjc
yjrtcjc

Reputation: 13

Sum Variables Conditionally in SAS EG

I am trying to create computed variables in SAS EG.

data data1;
    input ID Type Payment_Amt;
    cards;
    1 Voucher $50
    1 Cash $50
    1 Cash $20
    1 Card $20
    1 Card $50
;

Data want:

ID        TotalAmtVoucher        TotalAmtCash        TotalAmtCard
1                      $50                      $70                            $70

Is this possible? Please let me know if I am lacking any details needed. Thank you very much!

Upvotes: 0

Views: 333

Answers (2)

Hugs
Hugs

Reputation: 543

If the values for Types are known, static, and few you could do the following:

data data1;
    input ID : 8. Type : $char10. Payment_Amt : dollar4.;
    cards;
1 Voucher $50
1 Cash $50
1 Cash $20
1 Card $20
1 Card $50
2 Voucher $90
2 Cash $30
;
run;

proc sql;
    create table data_want as
    select ID,
           sum(ifn(Type="Voucher",Payment_Amt,0))   as TotalAmtVoucher format=dollar4.,
           sum(ifn(Type="Cash",Payment_Amt,0))      as TotalAmtCash    format=dollar4.,
           sum(ifn(Type="Card",Payment_Amt,0))      as TotalAmtCard    format=dollar4.
    from data1
    group ID;
quit;

Upvotes: 0

Andrew Haynes
Andrew Haynes

Reputation: 2640

Your input data for Payment_Amt is going to be a character variable with a $ on the front. which is going to be really awkward to work with. I suggest using the numeric amount and using the dollarformat to display them as monetary values.

However, if you're data is already in a character format, you can convert them to numeric with:

data data1;
  set data1;
  Payment_Amt2 = input(substr(Payment_Amt,2),best.);

  format Payment_Amt2 dollar3.;

  drop Payment_Amt;
  rename Payment_Amt2 = Payment_Amt;

run;

This takes only the values after the $ in the string using substr()and then converts them to numeric values with the input() function.

To get your totals you can use proc sql and then transpose the data:

proc sql;
  create table want0 as
  select distinct id, type, sum(payment_amt) as total
  from data1
  group by type;
quit;

proc transpose data = want0 out = want(drop = _name_) prefix = TotalAmt;
  by id;
  id type;
run;

The proc sql step will sum all the values of a particular type via the group by statement. You can then get the data into the format you want with proc transpose. The use of the prefix= option allows you to specify the "TotalAmt" prefix in you variable name.

Upvotes: 1

Related Questions