topic Proc SQL: When Then Else in SAS Procedures

Proc SQL: When Then Else

RobertNYC — Tue, 07 Feb 2012 01:30:16 GMT

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.

Proc SQL: When Then Else

art297 — Tue, 07 Feb 2012 01:51:08 GMT

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;

Proc SQL: When Then Else

Haikuo — Tue, 07 Feb 2012 01:55:54 GMT

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

Proc SQL: When Then Else

RobertNYC — Tue, 07 Feb 2012 02:12:10 GMT

This is great. Thanks.

Proc SQL: When Then Else

RobertNYC — Mon, 13 Feb 2012 02:57:20 GMT

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

Proc SQL: When Then Else

Haikuo — Mon, 13 Feb 2012 03:09:08 GMT

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