Hello,
Suppose I have following dataset (" test"):
id | var_1 | var_2 |
---|---|---|
1 | 1 | 1 |
1 | 2 | 1 |
1 | 3 | 0 |
1 | 4 | 0 |
1 | 5 | 1 |
2 | 6 | 1 |
2 | 7 | 0 |
2 | 8 | 0 |
2 | 9 | 1 |
2 | 10 | 1 |
I run follwoing codes:
proc sort data=test;
by id var_1 var_2;
run;
data test;
set test;
by id var_1 var_2;
if last.var_2 then var_3=1;
else var_3=0;
run;
What I expect is:
id | var_1 | var_2 | var_3 |
---|---|---|---|
1 | 1 | 1 | 0 |
1 | 2 | 1 | 1 |
1 | 3 | 0 | 0 |
1 | 4 | 0 | 1 |
1 | 5 | 1 | 0 |
2 | 6 | 1 | 1 |
2 | 7 | 0 | 0 |
2 | 8 | 0 | 1 |
2 | 9 | 1 | 0 |
2 | 10 | 1 | 1 |
However, what I get is:
id | var_1 | var_2 | var_3 |
---|---|---|---|
1 | 1 | 1 | 1 |
1 | 2 | 1 | 1 |
1 | 3 | 0 | 1 |
1 | 4 | 0 | 1 |
1 | 5 | 1 | 1 |
2 | 6 | 1 | 1 |
2 | 7 | 0 | 1 |
2 | 8 | 0 | 1 |
2 | 9 | 1 | 1 |
2 | 10 | 1 | 1 |
What I did is wrong?
To get your requested output use BY VAR_2 NOTSORTED.
Not sure that actually makes any sense as it is crossing boundry between values of ID.
159 data want ;
160 set have ;
161 by var_2 notsorted;
162 var_3=last.var_2;
163 put (id var_1 var_2 var_3) (3.);
164 run;
1 1 1 0
1 2 1 1
1 3 0 0
1 4 0 1
1 5 1 0
2 6 1 1
2 7 0 0
2 8 0 1
2 9 1 0
2 10 1 1
The problem is the VAR_1 is different on every observation. So within the set of constant values for ID and VAR_1 every value of VAR_2 is unique.
data want ;
set test;
by id var_2 notsorted;
var_3 = last.var_2;
run;
I think you are trying to do something like:
data test;
set test;
by id var_2 notsorted;
if last.var_2 then var_3=1;
else var_3=0;
run;
However, that would meet all of your expectations except for the 5th record which, if I correctly understand what you are trying to do, was incorrect in your example.
Thank you all very much for help. I never know there is a "notsorted" option.
By the way, could you please elaborate why 5th record is incorrect? I couldn't figure it out myself...
To get your requested output use BY VAR_2 NOTSORTED.
Not sure that actually makes any sense as it is crossing boundry between values of ID.
159 data want ;
160 set have ;
161 by var_2 notsorted;
162 var_3=last.var_2;
163 put (id var_1 var_2 var_3) (3.);
164 run;
1 1 1 0
1 2 1 1
1 3 0 0
1 4 0 1
1 5 1 0
2 6 1 1
2 7 0 0
2 8 0 1
2 9 1 0
2 10 1 1
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.