topic Re: If condition with By group in SAS Programming

If condition with By group

Xusheng — Fri, 26 Apr 2019 19:28:17 GMT

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.

Re: If condition with By group

PaigeMiller — Fri, 26 Apr 2019 19:29:50 GMT

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

Re: If condition with By group

ballardw — Fri, 26 Apr 2019 19:32:39 GMT

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.

Re: If condition with By group

Xusheng — Fri, 26 Apr 2019 19:34:17 GMT

Thank you, this makes more sense.

Re: If condition with By group

Xusheng — Fri, 26 Apr 2019 19:34:19 GMT

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?

Re: If condition with By group

PaigeMiller — Sun, 28 Apr 2019 10:39:43 GMT

@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"

Re: If condition with By group

Tom — Fri, 26 Apr 2019 23:04:10 GMT

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