Feeling quiet rusty so far with sums, could somebody help me to understand how to do this in a data step?
"Y" is a particular cumulative sum; it represents the sum of values in the subsequent obervations in x (for each group a and b).
ID | x | y |
---|---|---|
a | 1 | 4 |
a | 1 | 3 |
a | 0 | 2 |
a | 1 | 2 |
a | 1 | 1 |
b | 0 | 2 |
b | 1 | 2 |
b | 1 | 1 |
b | 0 | 0 |
b | 0 | 0 |
data have;
input ID $ x;
datalines;
a 1
a 1
a 0
a 1
a 1
b 0
b 1
b 1
b 0
b 0
;
data have1;
set have;
obsNo = _n_;
run;
proc sort data=have1; by id descending obsNo; run;
data want;
set have1;
by id;
if first.id then y=0;
y+x;
run;
proc sort data=want out=want(drop=obsNo); by id obsNo; run;
proc print; run;
That does it. - PG
Not sure exactly what you are asking but, given the data you presented, here is one way to achieve it:
data have;
input ID $ x;
n=_n_;
cards;
a 1
a 1
a 0
a 1
a 1
b 0
b 1
b 1
b 0
b 0
;
proc sort data=have;
by descending n;
run;
data want;
set have;
by descending id;
if first.id then y=0;
y+(x eq 1);
run;
proc sort data=want;
by n;
run;
Another alternative;
data want;
do until(last.id);
set have;
by id;
y+x;
end;
do until (last.id);
set have;
by id;
output;
y=y-x;
end;
run;
Regards,
Haikuo
data have;
input ID $ x;
datalines;
a 1
a 1
a 0
a 1
a 1
b 0
b 1
b 1
b 0
b 0
;
data have1;
set have;
obsNo = _n_;
run;
proc sort data=have1; by id descending obsNo; run;
data want;
set have1;
by id;
if first.id then y=0;
y+x;
run;
proc sort data=want out=want(drop=obsNo); by id obsNo; run;
proc print; run;
That does it. - PG
Thanks for your input guys !
I will test them tomorrow and keep you posted !
A+
And just for the sake of ecumenism :smileygrin: :
data have;
input ID $ x;
datalines;
a 1
a 1
a 0
a 1
a 1
b 0
b 1
b 1
b 0
b 0
;
data have1;
set have;
obsNo = _n_;
run;
proc sql;
create table want as
select A.ID, A.x, sum(B.x) as y
from have1 as A, have1 as B
where A.id=B.id and A.obsNo<=B.obsNo
group by A.id, A.obsNo, A.x
order by A.id, A.obsNo;
drop table have1;
proc print data=want; run;
Very good! Thanks a lot! And I had to look up " ecumenism".
data have;
input ID $ x;
n=_n_;
cards;
a 1
a 1
a 0
a 1
a 1
b 0
b 1
b 1
b 0
b 0
;
proc sort data=have;
by id descending n;
run;
data want(drop=n);
set have;
by id;
y+(-first.id*y)+x;
run;
proc sort data=want;
by id descending y x;
run;
proc print ;run;
Obs ID x y
1 a 1 4
2 a 1 3
3 a 0 2
4 a 1 2
5 a 1 1
6 b 0 2
7 b 1 2
8 b 1 1
9 b 0 0
10 b 0 0
Linin
Hi all,
I just wanted to thank you again.
I used the code from PGstats. It gave me exactly what I was looking for on the first attempt.
I tried a little bit the others, they we pretty close (and very interesting) for sure if I would have had more time I could have made them work as well !
Bonne soiree !!!
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!
Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.
Find more tutorials on the SAS Users YouTube channel.