Hello-
I am trying to write a statement that says "If Points is greater than 1.05 , then points is capped at 1.05 else points stay the same." I think i'm getting close.
Start:
POINTS |
---|
1.25 |
.77 |
.99 |
1.13 |
.03 |
1.03 |
.89 |
1.33 |
Desired Result:
NEWPOINTS |
---|
1.05 |
.77 |
.99 |
1.05 |
.03 |
1.05 |
.89 |
1.05 |
My code:
proc sql;
select
points,
case when points > 1.05 then end as newpoints
from have;
quit;
When I run the code above, I get kind of close. Every value that's greater than 1.05 turns to 1.05 but everything else turns to '.' .
Result:
NewPoints |
---|
1.05 |
. |
. |
1.05 |
. |
1.05 |
. |
1.05 |
I think i have to add "else" but if so, I don't know what to put that will say "Do Nothing" or "bring the same value".
proc sql;
select
points,
case when points > 1.05 then end else..... as newpoints
from have;
quit;
Any assistance would be great. Thanks.
data have;
input POINTS;
cards;
1.25
.77
.99
1.13
.03
1.03
.89
1.33
;
proc sql;
create table want as
select points,
case when points ge 1.05 then 1.05
else points
end as newpoints
from have
;
quit;
data have;
input POINTS;
cards;
1.25
.77
.99
1.13
.03
1.03
.89
1.33
;
proc sql;
create table want as
select points,
case when points ge 1.05 then 1.05
else points
end as newpoints
from have
;
quit;
This is great. Thanks.
I know you were asking SQL solution and Art just offered one. In case data step is not completely out of picture, here is one example:
data want;
set have;
points=ifn(points>1.05,1.05,points);
run;
Regards,
Haikuo
Hi Hai.kuo--
I tried to run your code and it did not work. I recived an error messsage that was like "If" is not vali.
Id the code supposed to be:
newpoints=ifn(oldpoints>1.05,1.05,oldpoints);
and is it ifn or just if?
Thanks
That is odd. I can't repeat your error. Please provide your error part of your log. Here is what I have just tried:
data have;
input POINTS;
cards;
1.25
.77
.99
1.13
.03
1.03
.89
1.33
;
data want;
set have;
points=ifn(points>1.05,1.05,points);
run;
proc print;run;
And this was my log:
587 data have;
588
589 input POINTS;
590
591 cards;
NOTE: SAS went to a new line when INPUT statement reached past the e
NOTE: The data set WORK.HAVE has 8 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
601 ;
602
603 data want;
604
605 set have;
606
607 points=ifn(points>1.05,1.05,points);
608
609 run;
NOTE: There were 8 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 8 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.12 seconds
cpu time 0.01 seconds
610
611 proc print;run;
NOTE: There were 8 observations read from the data set WORK.WANT.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
And this is the output:
Obs POINTS
1 1.05
2 0.77
3 0.99
4 1.05
5 0.03
6 1.03
7 0.89
8 1.05
Everything seems normal to me.
Ifn() is a function, if is a statement. In this case, you can do it both ways. However, in the context of my code, it has to be ifn(). You need to rephrase the code to be able to use 'if'. For details, please refer to SAS manual.
Haikuo
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.