Obsidian | Level 7

Hi Everyone,

I'm looking for some assistance from you all regarding the topic which is:

I've a numeric column named AMOUNT

Required output:
name        Amount
X           12345
Y            5709
Z            56
A            10
Sum         18120
Data have;
Input name \$5. Amount 10.;
Datalines;
X 12345
Y 5709
Z 56
A 10;

which has 4 values. I would like to add the sum of these 4 values as the 5th  value  of this existing column and table. Please advise. Thanks in advance.

5 REPLIES 5
SAS Super FREQ

Try something like this:

Data have;
Input name \$5. Amount 10.;
Datalines;
X     12345
Y     5709
Z     56
A     10
;
run;

Data want;
retain sum 0;
set have end=eof;
sum + amount;
if eof then do;
output;
name = "sum";
amount = sum;
output;
end;
else output;
drop sum;
run;
Obsidian | Level 7

It worked. Thankyou. And if possible. , Can we find the percentage for each name variable value.

I.e

Req output:
Name  Amount  percentage
X     12345      68%
Y     5709       0%
Z      56        0%
A      10        0%
Sum   18120

Here percentage is calculated by dividing the Each individual (amount/sum)*100.
Super User

The tool for creating reports (nobody needs a summary row in a dataset) is PROC REPORT:

Data have;
Input name :\$5. Amount :10.;
Datalines;
X 12345
Y 5709
Z 56
A 10
;

proc report data=have;
column name amount pct;
define name / display;
define amount / analysis;
define pct / computed format=percent9.1;
compute before;
tot = amount.sum;
endcomp;
compute pct;
pct = amount.sum / tot;
endcomp;
compute after;
name = "Total";
endcomp;
rbreak after / summarize;
run;
Obsidian | Level 7

Thanks a bunch.
Super User

One of the reasons for not adding variables that represent summaries of multiple records on a "total" row or similar in a data set is that quite often you get further into your analysis you realize that you need to add another variable. If you aren't extremely careful and do this is with data including summaries you can create incorrect or misleading values for such added variables on the record with the summary.

Another is if you borrow the code for another project and forget that this summary is added to the data you may accidentally add an unintended summary and possibly include that previous summary value in to a new summary.

When such summary is included in the data then many report or modeling procedures will yield very inaccurate results.

Discussion stats
• 5 replies
• 637 views
• 2 likes
• 4 in conversation