If condition with By group

[Xusheng]

Hi, I was trying to make the first return of each day equals to 0 and keep the rest returns unchanged. 'Return' is the variable that I calculated based on price. 

Here is the sample of Have:

data have;
input Interval Return date ：ddmmyy10. ;
datalines;
1 23	03/09/2012
2 32	03/09/2012
1 45	04/09/2012
2 12	04/09/2012
3 56	04/09/2012
1 43	05/09/2012
1 76	06/09/2012
2 43	06/09/2012
3 34	06/09/2012
4 54	06/09/2012
1 39	07/09/2012
1 54	08/09/2012
2 43	08/09/2012
1 65	10/09/2012


run;

Want:

data Want;
input Interval Return date ：ddmmyy10. ;
datalines;
1 0	03/09/2012
2 32	03/09/2012
1 0	04/09/2012
2 12	04/09/2012
3 56	04/09/2012
1 0	05/09/2012
1 0	06/09/2012
2 43	06/09/2012
3 34	06/09/2012
4 54	06/09/2012
1 0	07/09/2012
1 0	08/09/2012
2 43	08/09/2012
1 0	10/09/2012


run;

Here is the code I tried:

Proc sort data=Have;
	By Date Interval;
Run;

Data Want;
	Set Have;	
		By Date Interval;
		if first.Date or  first.Interval then Return = 0;
run;

I got all the returns turned to 0. Is there any suggestion? Thank you in advance.

[PaigeMiller · Accepted solution]

Data Want;
	Set Have;	
		By Date Interval;
		if first.Date then Return = 0;
run;

[Xusheng]

Thank you, that would work, but I still quite confused why first.interval
wouldn't work here. Is that a group that is nested inside the date?

[PaigeMiller]

---

@Xusheng wrote:
Thank you, that would work, but I still quite confused why first.interval

---

Here is the explanation

You asked for "I was trying to make the first return of each day equals to 0" which in SAS code translates to 

if first.date then return=0;

First.interval does NOT produce the stated requirement of "I was trying to make the first return of each day equals to 0" 

[Tom]

When you use multiple variables in a BY statement then yes they are "nested".  So the first variable divides the data into groups. The second variable in the BY list is then used to divide those groups up into even smaller groups.

So if you use 

by date interval;

then FIRST.INTERVAL will be true when the value of INTERVAL changes WITHIN the current value of DATE.

So if FIRST.DATE is true then FIRST.INTERVAL has to also be true because if it is the first observation for that value of DATE then it must also by definition be the first observation for that value of INTERVAL within that value of DATE.

Here is a simple example:

date interval first.date last.date first.interval last.interval
2018/01/01 A 1 0 1 0
2018/01/01 A 0 0 0 1
2018/01/01 B 0 0 1 1
2018/01/01 C 0 1 1 1

[ballardw]

Data Want;
	Set Have;	
		By Date ;
		if first.Date  then Return = 0;
run;

At least in your example data there were no duplicates of interval within a date so every record was true for first.interval.

[Xusheng]

Thank you, this makes more sense.