topic Re: How to output sum of a varable in the same dataset in SAS Data Management

How to output sum of a varable in the same dataset

jjjunyi — Sat, 24 Jan 2015 00:01:45 GMT

Hi Every one,

The data A is

Date	AZ
1/1/2015	10
1/1/2016	45
1/1/2017	41
1/1/2018	41
1/1/2019	46
1/1/2020	65
1/1/2021	43
1/1/2022	59

I want it to be

Date	AZ
1/1/2015	10
1/1/2016	45
1/1/2017	41
1/1/2018	41
1/1/2019	46
1/1/2020	65
1/1/2021	43
1/1/2022	59
Total	350

Re: How to output sum of a varable in the same dataset

Patrick — Sat, 24 Jan 2015 00:52:04 GMT

The are many ways of how to achieve this (proc print, proc report, proc tabulate, proc sql, proc summary, ....). What have you tried so far and where did you get stuck?

Re: How to output sum of a varable in the same dataset

Ksharp — Sat, 24 Jan 2015 08:35:14 GMT

If date variable is character type, that would be easy. otherwise you need other skill.

data have;
input Date : $20.     AZ ;
cards;
1/1/2015     10
1/1/2016     45
1/1/2017     41
1/1/2018     41
1/1/2019     46
1/1/2020     65
1/1/2021     43
1/1/2022     59
;
run;
data want;
 set have end=last;
 sum+az;output;
 if last then do;
   date='Total';az=sum;output;
 end;
 drop sum;
run;

Xia Keshan

Re: How to output sum of a varable in the same dataset

LinusH — Mon, 26 Jan 2015 12:37:40 GMT

Please, don't store totals in the same table as the detail data.

As Patrick suggests, creating totals is the work for reports, not data manipulation steps.

Re: How to output sum of a varable in the same dataset

RW9 — Mon, 26 Jan 2015 13:12:03 GMT

I actually disagree with this. A case in point is my deliverables, I always store a dataset almost exactly as it is shown in the output, so that this can be independently QC'd. Reduces the amount of QC done on the output file. Of course this wouldn't alter the source data, just the processed data going into the output files. Of course, something simple like this you could do with proc report for instance and have that output a dataset, however as it gets more complicated...

Re: How to output sum of a varable in the same dataset

Patrick — Tue, 27 Jan 2015 08:28:17 GMT

Storing summary rows in a table is... let's call it "very uncommon" and I would assume you'd be pretty alone with such an approach.

Re: How to output sum of a varable in the same dataset

TomKari — Tue, 27 Jan 2015 14:10:25 GMT

I strongly side with LInus and Patrick.

I've had considerable experience with managing aggregated data stores, and this architecture will greatly increase the chances that you'll produce incorrect summarized results in the future.

However, if you're going to do this, use something like the _TYPE_ variable that PROC MEANS creates, to provide an unambiguous indication of the role that each row plays.

Tom

Re: How to output sum of a varable in the same dataset

RW9 — Tue, 27 Jan 2015 14:33:11 GMT

Possibly a difference in industries/usage there then. My outputs are of the form:

col1 col2 treat1 treat2 total

xyz x n (n%) n (n%) n (%)

...

And some other slightly more complicated. I am not for a moment suggesting this should be stored in a data warehouse or what not, this is purely a dataset to support an output, and whilst with some compute blocks you may be able to get the aforementioned output we need to retain a useable copy of this so an independent QC programmer can re-create the same data for a comparison.

Note, I am not changing the raw data, merely the output supporting dataset.

Re: How to output sum of a varable in the same dataset

LinusH — Tue, 27 Jan 2015 15:06:39 GMT

For temporary tables which existence is purely for report out, then it serves a purpose.

So perhaps we agree on this matter for the main situations...?