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 |
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;
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;
@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 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.
How many distinct values of name does your real dataset have? How many months?
/*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;
Proc SQL solution was pending, so here it is:
Transpose.
Transpose.
Transpose.
Having data (months) in structure (variable names) is (almost) always a BAD IDEA.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: