BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RobertNYC
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

5 REPLIES 5
art297
Opal | Level 21

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
Obsidian | Level 7

This is great. Thanks.

Haikuo
Onyx | Level 15

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
Obsidian | Level 7

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
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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