topic Re: calculated row in SAS Programming

calculated row

ss171 — Mon, 18 Dec 2023 09:57:42 GMT

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

Re: calculated row

rudfaden — Mon, 18 Dec 2023 11:04:11 GMT

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;

Re: calculated row

s_lassen — Mon, 18 Dec 2023 11:05:05 GMT

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;

Re: calculated row

PaigeMiller — Mon, 18 Dec 2023 12:20:27 GMT

@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).

Re: calculated row

Ksharp — Tue, 19 Dec 2023 02:30:46 GMT

/*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;

Re: calculated row

Aku — Tue, 19 Dec 2023 13:33:12 GMT

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;

Re: calculated row

ss171 — Wed, 20 Dec 2023 12:06:24 GMT

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.

Re: calculated row

PaigeMiller — Wed, 20 Dec 2023 14:26:12 GMT

@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.

Re: calculated row

Kurt_Bremser — Wed, 20 Dec 2023 12:59:35 GMT

Transpose.

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

Re: calculated row

Kurt_Bremser — Wed, 20 Dec 2023 13:00:49 GMT

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

Re: calculated row

mkeintz — Wed, 20 Dec 2023 16:20:58 GMT

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.