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 !!!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.