Something similar to INF (from R) in SAS

[whs278]

Is there a way to code a missing value so that when comparing it to any numeric value it will be considered greater?

Essentially, I want something equivalent to an 'INF' in R.

Here is some code to help explain what I want.  Let's say I want to find the lightest apple I bought on a given shopping date and I want to assign that value to every case in that shopping date group regardless of whether the item is an apple or not. 

I use the following code.  However, I want to make sure that if the lightest item in the shopping date group is not an apple, it won't be considered.  I do this by temporarily assigning all non-apple items a weight of "INF", i.e. some value greater than all possible apple weights.  

PROC SQL;

CREATE TABLE WANT AS 

SELECT *, MIN(CASE WHEN TYPE = 'APPLE', WEIGHT, ELSE INF) AS LIGHTEST_APPLE

FROM HAVE

GROUP BY SHOPPING_DATE;

QUIT;

My only issue is that SAS does not have anything that I know that is equivalent to "INF" in R.  Is there a better way to accomplish this same goal in SAS?

Thanks for any help. 

[novinosrin]

Hi @whs278  Are you after this by any chance. I don't know what R is. But I am taking a guess

 

PROC SQL;

 

CREATE TABLE WANT AS 

SELECT *, min(ifn(TYPE = 'APPLE',WEIGHT,.)) AS LIGHTEST_APPLE

FROM HAVE

GROUP BY SHOPPING_DATE;

QUIT;

[Tom]

SAS has 28 missing values. Normal missing value is represented by period and the others by a period followed by a single letter or an underscore.  You can use anyone of them to mean positive infinity if you want.  So you could decide to use .I to represent positive infinity.

But note that all missing values are considered less than any actual number when compared with an inequality operator.  And they are all ignored by the MIN() function.

CREATE TABLE WANT AS 
  SELECT
    SHOPPING_DATE
  , MIN(CASE WHEN TYPE = 'APPLE' then WEIGHT else . end) as LIGHTEST_APPLE
  , case when (missing(calculated LIGHTEST_APPLE)) then .I else . end 
 as LIGHTEST_APPLE_OR_INFINITY
  FROM HAVE
  GROUP BY SHOPPING_DATE
;

[SuryaKiran · Accepted solution]

INF in R is infinity, so you want to put some value that is very high. Simply give a very high number.

proc sql;
create table want as 
select *, min(case when sex='M' then Height else 9999999 end)  as least
from sashelp.class;
quit;

or if your not sure about giving a high number then give the max from that table itself so that it will not be min.

proc sql;
create table want as 
select *, min(case when sex='M' then Height else (select max(height)+99 from sashelp.class) end)  as least
from sashelp.class;
quit;

[ballardw]

I think you may just need to recast how you use MIN. MIn will return the lowest non-missing value if there are any.

But without data I'm not sure how to recast your specific query.