Proc SQL: When Then Else

[RobertNYC]

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.

[art297 · Accepted solution]

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;

[RobertNYC]

This is great. Thanks.

[Haikuo]

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

[RobertNYC]

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

[Haikuo]

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