calculated row

[ss171]

Hi , Three is small help needed 

I have the below dataset 

data sample;
input name$ june july;
datalines ;
amount 170 140
account 2 4
;
run;

I need to create new row average which is amount/account 

output :

name	june	july
amount	170	140
account	2	4
average	85	35

[rudfaden]

you can transpose it for a better structure. If you need it in the old form, you can transpose it back.

data sample;
input name$ june july;
datalines ;
amount 170 140
account 2 4
;
run;

proc transpose data=sample out=long;
id name;
run;

data want;
set long;
avg=amount/account;
rename _NAME_= Month;
run;

[s_lassen]

You are probably better off with a transposed dataset (you may have some other identifying variables on the input data, which can be put in a BY statement):

proc transpose data=sample out=transposed(rename=(_name_=month));
  var june--july;
  id name;
run;

Then you can quite easily calculate the average:

data calc;
  set transposed;
  average=amount/account;
run;

If you MUST have things back, just transpose again:

proc transpose data=calc out=want(rename=(_name_=name));
  var amount account average;
  id month;
run;

[PaigeMiller]

---

@ss171 wrote:

Hi , Three is small help needed 

I have the below dataset 

data sample;
input name$ june july;
datalines ;
amount 170 140
account 2 4
;
run;

I need to create new row average which is amount/account 

output :

name	june	july
amount	170	140
account	2	4
average	85	35

---

I don't understand the question — average of what? Are you trying to average the numbers shown next to amount and next to account? That makes no sense.

Furthermore, as others have pointed out, this is a very very poor layout to do anything in SAS. Calendar information (such as "June") should never be stored in variable names. It should be stored as the value of a variable named Month. Furthermore, dates in SAS are best set up as integers representing the number of days since 01JAN1960, and then all of the work SAS has done to properly deal with dates and months and weeks and years is available to you, and you don't have to figure out how to handle dates yourself.

Example: 

data have;
    input month :date9. amount account;
    format month monyy7.;
    cards;
01MAR2023 170 2
01APR2023 140 4
;

I do not include in the above code a method of averaging, as you have not explained the averages that you want. However, if you want the average over all months of amount, and the average over all months of account, this will provide the average (which is different than what you show)

proc means data=have mean;
    var amount account;
run;

This is a benefit of having the proper layout of your data, then every SAS PROC is available and will make your calculations easier. The results of PROC MEANS could be appended to your data if that's what you want (I do not show that code as I am still not sure what averages you want).

[ss171]

Thanks @Paige Miller, but this is just a dummy data which I have created to understand the way of doing. Actual data is something different only.

[PaigeMiller]

---

@ss171 wrote:
Thanks @Paige Miller, but this is just a dummy data which I have created to understand the way of doing. Actual data is something different only.

---

Great. As everyone has stated, you have data in a very poor layout for working in SAS. You would find it much easier to transpose the data rather than work with the data in the layout you show.

[Kurt_Bremser]

How many distinct values of name does your real dataset have? How many months?

[Ksharp]

/*Here is SAS/IML solution.*/
data sample;
input name$ june july;
datalines ;
amount 170 140
account 2 4
;

proc iml;
use sample;
read all var{name};
read all var _num_ into x[c=vname];
close;
name=name//'average';
want=x//(x[1,]/x[2,]);
create want from want[r=name c=vname];
append from want[r=name];
close;
quit;

[Aku]

Proc SQL solution was pending, so here it is:

proc sql;

  select name as name, 

         june,

july

  from sample

  union

  select 'average' as name, 

         mean(june) as june,

mean(july) as july

  from sample;

quit;

[Kurt_Bremser]

Transpose.

Transpose.

Transpose.

Having data (months) in structure (variable names) is (almost) always a BAD IDEA.

[mkeintz]

I generally agree with the conventional wisdom on the advantages of structuring data in a long vs wide format.

But in this case - 2 rows (a sum row followed by a frequency row), a set of ID variables (only NAME in this case), and any number of columns, there is really no need for transpose, assuming you really do want an additional row of averages:

data sample;
input name$ june july;
datalines ;
amount 170 140
account 2 4
run;

data want;
  set sample  end=end_of_sample;
  output;
  array vals _numeric_;
  do over vals;
    vals=lag(vals)/vals;
  end;
  if end_of_sample then do;
    name='Average';
    output;
  end;
run;

This will produce the following log note:

NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      2 at 19:19

which can be avoided by using

    vals=sum(0,lag(vals))/vals;

inside the "DO OVER VALS" loop.