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.
Data Want;
Set Have;
By Date Interval;
if first.Date then Return = 0;
run;
Data Want;
Set Have;
By Date Interval;
if first.Date then Return = 0;
run;
@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"
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
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.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.