I have a data as follows and I'm trying to find the cumilative sum of the variable 'VALUE' with Group by of the variable 'TYPE' '. I tried with sum(value) and group by the variable 'TYPE' but it did not produce the desired results. When I say cumulative sum, means I want to sum the values of the current rows and the subsequent rows and not the value from previous rows.
Looking for some insights to get it done. Hope someone helps
Source data:
UNIT CAL_MONTH TYPE VALUE 11223 30.06.2019 LR 20 11223 30.09.2019 LR 20 11223 31.12.2019 LR 20 11223 31.03.2020 LR 20 11223 30.06.2020 LR 46 11223 30.09.2020 LR 46 11223 31.12.2020 LI 46 11223 31.03.2021 LI 26 11223 30.06.2021 LI 40 11223 30.09.2021 LI 40 11223 31.12.2021 LI 40 11223 31.03.2022 LI 14
Desired result is,
UNIT CAL_MONTH TYPE VALUE 11223 30.06.2019 LR 172 11223 30.09.2019 LR 152 11223 31.12.2019 LR 132 11223 31.03.2020 LR 112 11223 30.06.2020 LR 66 11223 30.09.2020 LR 20 11223 31.12.2020 LI 206 11223 31.03.2021 LI 160 11223 30.06.2021 LI 134 11223 30.09.2021 LI 94 11223 31.12.2021 LI 54 11223 31.03.2022 LI 40
proc sql;
create table _temp as
select *, sum(value) as total_value
from have
group by unit, type;
quit;
data want;
set have;
by unit type;
retain remaining;
if first.type then remaining = total_value;
else remaining = total_value - value;
run;
@David_Billa wrote:
I have a data as follows and I'm trying to find the cumilative sum of the variable 'VALUE' with Group by of the variable 'TYPE' '. I tried with sum(value) and group by the variable 'TYPE' but it did not produce the desired results.
Looking for some insights to get it done. Hope someone helps
Source data:
UNIT CAL_MONTH TYPE VALUE 11223 30.06.2019 LR 20 11223 30.09.2019 LR 20 11223 31.12.2019 LR 20 11223 31.03.2020 LR 20 11223 30.06.2020 LR 46 11223 30.09.2020 LR 46 11223 31.12.2020 LI 46 11223 31.03.2021 LI 26 11223 30.06.2021 LI 40 11223 30.09.2021 LI 40 11223 31.12.2021 LI 40 11223 31.03.2022 LI 14Desired result is,
UNIT CAL_MONTH TYPE VALUE 11223 30.06.2019 LR 172 11223 30.09.2019 LR 152 11223 31.12.2019 LR 132 11223 31.03.2020 LR 112 11223 30.06.2020 LR 66 11223 30.09.2020 LR 20 11223 31.12.2020 LI 206 11223 31.03.2021 LI 160 11223 30.06.2021 LI 134 11223 30.09.2021 LI 94 11223 31.12.2021 LI 54 11223 31.03.2022 LI 40
Please provide the data in a useable form ... essentially we need working SAS data step code. Thanks!
@David_Billa wrote:
I'm looking for logic so that I can manage myself to implement it.
This doesn't really address my issue, nor does it seem to address @ballardw's issue.
Please describe in excruciating detail how the want value of
11223 30.09.2020 LR 20
is arrived at.
Since there is an existing value of
11223 30.09.2020 LR 46
That 20 is not in any way a "sum". So I am not surprised that a by group approach did not yield what you want since your not actually summing things in the normal meaning of "sum".
proc sql;
create table _temp as
select *, sum(value) as total_value
from have
group by unit, type;
quit;
data want;
set have;
by unit type;
retain remaining;
if first.type then remaining = total_value;
else remaining = total_value - value;
run;
@David_Billa wrote:
I have a data as follows and I'm trying to find the cumilative sum of the variable 'VALUE' with Group by of the variable 'TYPE' '. I tried with sum(value) and group by the variable 'TYPE' but it did not produce the desired results.
Looking for some insights to get it done. Hope someone helps
Source data:
UNIT CAL_MONTH TYPE VALUE 11223 30.06.2019 LR 20 11223 30.09.2019 LR 20 11223 31.12.2019 LR 20 11223 31.03.2020 LR 20 11223 30.06.2020 LR 46 11223 30.09.2020 LR 46 11223 31.12.2020 LI 46 11223 31.03.2021 LI 26 11223 30.06.2021 LI 40 11223 30.09.2021 LI 40 11223 31.12.2021 LI 40 11223 31.03.2022 LI 14Desired result is,
UNIT CAL_MONTH TYPE VALUE 11223 30.06.2019 LR 172 11223 30.09.2019 LR 152 11223 31.12.2019 LR 132 11223 31.03.2020 LR 112 11223 30.06.2020 LR 66 11223 30.09.2020 LR 20 11223 31.12.2020 LI 206 11223 31.03.2021 LI 160 11223 30.06.2021 LI 134 11223 30.09.2021 LI 94 11223 31.12.2021 LI 54 11223 31.03.2022 LI 40
To add the total in to the table to use to decrement your count.
You can accomplish your goal like this:
data have;
informat Cal_Month ddmmyy10.;
format Cal_Month ddmmyy10.;
input UNIT CAL_MONTH TYPE $ VALUE;
cards;
11223 30.06.2019 LR 20
11223 30.09.2019 LR 20
11223 31.12.2019 LR 20
11223 31.03.2020 LR 20
11223 30.06.2020 LR 46
11223 30.09.2020 LR 46
11223 31.12.2020 LI 46
11223 31.03.2021 LI 26
11223 30.06.2021 LI 40
11223 30.09.2021 LI 40
11223 31.12.2021 LI 40
11223 31.03.2022 LI 14
;run;
data want;
set have;
by unit type notsorted;
if first.type then do;
sum=0;
do until(last.type2);
set have(rename=(type=type2) keep=unit type value);
by unit type2 notsorted;
sum+value;
end;
end;
output;
sum=sum-value;
drop value type2;
rename sum=value;
run;
The NOTSORTED option on the BY statements is because the data is not sorted by TYPE - I assume that you want to keep the date order. SUM is automatically retained because of the SUM statement ("sum+value"). I renamed TYPE to TYPE2 on the second SET statement, so that there will be no confusion between the two SET statements' FIRST. condition.
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 25. 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.