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.
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;
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;
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
;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.