I've the data as I mentioned in the data step below. I want to calculate the field 'VALUE' by 'TYPE' variable. Calculation of the field VALUE should be on the current value and the subsequent values by TYPE.
E.g. If you see I've 13 observations for TYPE LR. To calculate VALUE, first I have to sum of all rows (1st value to till 13th value) for the first record by TYPE then for the second record I've to sum from 2nd value to till 13th value and for 3rd record I want to sum from 3rd value to till 13th value and goes on till last record.
Client has generated the desired results in MS Excel using SUMIFS function and I find difficult to achieve it using SAS.
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 30.12.2020 LR 46 11223 31.03.2021 LR 26 11223 30.06.2021 LR 40 11223 30.09.2021 LR 40 11223 31.12.2021 LR 40 11223 31.03.2022 LR 14 11223 31.12.2020 LI 22 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;
Program which I tried is,
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;
Results which I got is,
Cal_Month UNIT TYPE value 30.06.2019 11223 LR 378 30.09.2019 11223 LR 364 31.12.2019 11223 LR 344 31.03.2020 11223 LR 324 30.06.2020 11223 LR 304 30.09.2020 11223 LR 258 30.12.2020 11223 LR 212 31.03.2021 11223 LR 166 30.06.2021 11223 LR 140 30.09.2021 11223 LR 100 31.12.2021 11223 LR 60 31.03.2022 11223 LR 20 31.12.2020 11223 LI 182 31.03.2021 11223 LI 168 30.06.2021 11223 LI 142 30.09.2021 11223 LI 102 31.12.2021 11223 LI 62 31.03.2022 11223 LI 22
Results which I want is,
Cal_Month UNIT TYPE value 30.06.2019 11223 LR 378 30.09.2019 11223 LR 358 31.12.2019 11223 LR 338 31.03.2020 11223 LR 318 30.06.2020 11223 LR 298 30.09.2020 11223 LR 252 30.12.2020 11223 LR 206 31.03.2021 11223 LR 160 30.06.2021 11223 LR 134 30.09.2021 11223 LR 94 31.12.2021 11223 LR 54 31.03.2022 11223 LR 14 31.12.2020 11223 LI 182 31.03.2021 11223 LI 160 30.06.2021 11223 LI 134 30.09.2021 11223 LI 94 31.12.2021 11223 LI 54 31.03.2022 11223 LI 14
I tried with retain statement and first. logic as well, but it's not working either. Only first value of each data type matches and others don't. Appericiate if someone of you help me get the desired results.
alternatively try
proc sort data=have;
by UNIT TYPE CAL_MONTH;
run;
data want;
do until(last.type);
set have;
by UNIT TYPE CAL_MONTH;
if first.type then value2=.;
if value ne . then value2+value;
value3=value2;
end;
do until(last.type);
set have;
by UNIT TYPE CAL_MONTH;
output;
value3=value3-value;
drop value2 value;
end;
run;
Hi @David_Billa Please see if this helps
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 30.12.2020 LR 46
11223 31.03.2021 LR 26
11223 30.06.2021 LR 40
11223 30.09.2021 LR 40
11223 31.12.2021 LR 40
11223 31.03.2022 LR 14
11223 31.12.2020 LI 22
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;
do _n_=1 by 1 until(last.type);
set have;
by unit type notsorted;
_value=sum(_value,value);
end;
do _n_=1 to _n_;
set have;
output;
_value=_value-value;
end;
run;
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 30.12.2020 LR 46
11223 31.03.2021 LR 26
11223 30.06.2021 LR 40
11223 30.09.2021 LR 40
11223 31.12.2021 LR 40
11223 31.03.2022 LR 14
11223 31.12.2020 LI 22
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;
proc sort; by Unit Type Cal_Month;
proc means data = have nway missing noprint;
var value;
class Unit Type;
output out=have_sum sum(value) = value_sum;
run;
data want(rename=(running_sum=value));
merge have have_sum;
by Unit Type;
retain running_sum prv_value 0;
if first.type then do;
running_sum=value_sum;
prv_value = Value;
end;
else do;
running_sum=running_sum - prv_value;
prv_value=value;
end;
keep Unit Type Cal_month running_sum;
run;
Solution you are looking for
alternatively try
proc sort data=have;
by UNIT TYPE CAL_MONTH;
run;
data want;
do until(last.type);
set have;
by UNIT TYPE CAL_MONTH;
if first.type then value2=.;
if value ne . then value2+value;
value3=value2;
end;
do until(last.type);
set have;
by UNIT TYPE CAL_MONTH;
output;
value3=value3-value;
drop value2 value;
end;
run;
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.