topic Re: cumulative sum in SAS Programming

cumulative sum

BrahmanandaRao — Fri, 06 Dec 2019 09:27:34 GMT

data ds;
input  x;
datalines;
1
2
3
4
5
-6
-7
-8
-9
;
run;

data t;
set ds;
if x>0 then do 
y=x+x	; 
if x<0 then do
y=x+x;
end;
end;
if x ge 0 then output;
if y le 0	then output;
run;

Here i am trying sum first five numbers and also do sum negivtive numbers in flag variable

Re: cumulative sum

PeterClemmensen — Fri, 06 Dec 2019 09:31:39 GMT

What does your desired result look like here?

Re: cumulative sum

BrahmanandaRao — Fri, 06 Dec 2019 09:36:11 GMT

HI Draycut

i want first 1 to 5 cumulative and also -6 to -9 values cumulative sum

Re: cumulative sum

Jagadishkatam — Fri, 06 Dec 2019 10:13:39 GMT

please try

data ds;
input  x;
if x gt 0 then grp=1;
else grp=2;
datalines;
1
2
3
4
5
-6
-7
-8
-9
;
run;

proc sql;
create table want as select sum(x) as sum, grp from ds group by grp;
quit;

Re: cumulative sum

ed_sas_member — Fri, 06 Dec 2019 10:43:15 GMT

Hi @BrahmanandaRao

Here is an alternative method:

data ds;
	input x;
	if x>0 then	group=1;
	else if x<0 then group=2;
	datalines;
1
2
3
4
5
-6
-7
-8
-9
;
run;

proc means data=ds sum noprint;
	var x;
	by group;
	output out=t (drop=_type_ _freq_) sum=grand_sum;
run;

Re: cumulative sum

novinosrin — Fri, 06 Dec 2019 13:37:37 GMT


data ds;
input  x;
datalines;
1
2
3
4
5
-6
-7
-8
-9
;
run;
/*SQL*/
proc sql;
create table want as
select sum(x) as s
from ds
group by sign(x);
quit;

/*Datastep Hash method*/
data want;
 if _n_=1 then do;
   dcl hash H () ;
   h.definekey  ("_n_") ;
   h.definedata ("sum") ;
   h.definedone () ;
 end;
 set ds end=z;
 if h.find(key:sign(x))=0 then sum=sum(sum,x);
 else sum=x;
 h.replace(key:sign(x),data:sum);
 if z;
 dcl hiter hi('h');
 do while(hi.next()=0);
  output;
 end;
 drop x;
run;


data want;
set ds end=z;
if _n_>1 and sign(x) ne lag(sign(x)) then do;
sum=0;
end;
sum+x;
run;

Re: cumulative sum

Astounding — Fri, 06 Dec 2019 15:40:10 GMT

Somehow this looks like using a sledgehammer to swat a fly. This should be pretty close to the mark:

data want;
   set have end=done;
   if x > 0 then positive_total + x;
   else negative_total + x;
   if done;
run;

Re: cumulative sum

DanielLangley — Fri, 06 Dec 2019 15:43:50 GMT

Does this not need a retain statement?

Re: cumulative sum

Astounding — Fri, 06 Dec 2019 15:51:26 GMT

No, this syntax automatically retains:

Varname + value;

Re: cumulative sum

Kurt_Bremser — Fri, 06 Dec 2019 17:25:13 GMT

@Astounding wrote:

Somehow this looks like using a sledgehammer to swat a fly. This should be pretty close to the mark:
data want;
   set have end=done;
   if x > 0 then positive_total + x;
   else negative_total + x;
   if done;
run;

This meets Saint-Exupery's definition of perfection.