Help using Base SAS procedures

Proc SQL: When Then Else

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

Proc SQL: When Then Else

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.


Accepted Solutions
Solution
‎02-06-2012 08:51 PM
PROC Star
Posts: 7,468

Proc SQL: When Then Else

Posted in reply to RobertNYC

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;

View solution in original post


All Replies
Solution
‎02-06-2012 08:51 PM
PROC Star
Posts: 7,468

Proc SQL: When Then Else

Posted in reply to RobertNYC

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;

Frequent Contributor
Posts: 101

Proc SQL: When Then Else

This is great. Thanks.

Respected Advisor
Posts: 3,156

Proc SQL: When Then Else

Posted in reply to RobertNYC

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

Frequent Contributor
Posts: 101

Proc SQL: When Then Else

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

Respected Advisor
Posts: 3,156

Proc SQL: When Then Else

Posted in reply to RobertNYC

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 2213 views
  • 0 likes
  • 3 in conversation