I use max function in Proc SQL its working fine.
MIN function not working properly giving wrong results. Some of them giving me 0 instead of values.
example:
min(case when region = 'SOU' then amt else 0 end) as MIN_sou,
min(case when region = 'NOR' then amt else 0 end) as MIN_nor
,
It is just doing what you asked.
Consider this two observation dataset.
data have ;
input regsion $ amt ;
cards;
SOU 100
NOR 150
;
The first CASE will return 0 for the second row and the second CASE will return 0 for the first row.
You should use missing value instead of 0 when the region does not match the target minimum.
,min(case when region = 'SOU' then amt else . end) as MIN_sou
,min(case when region = 'NOR' then amt else . end) as MIN_nor
You probably should have done the same for your MAX() calculations, but perhaps you didn't have any regions where the actual maximum amount was negative you didn't notice it wasn't working.
It could be that 0 is exactly what you asked for with your code (although it may not be what you wanted to get).
But ... we don't know what's in your data, and we don't really know what you want to get as a result ... so could you please show us a representative portion of your data, and explain exactly what result you want to get?
It is just doing what you asked.
Consider this two observation dataset.
data have ;
input regsion $ amt ;
cards;
SOU 100
NOR 150
;
The first CASE will return 0 for the second row and the second CASE will return 0 for the first row.
You should use missing value instead of 0 when the region does not match the target minimum.
,min(case when region = 'SOU' then amt else . end) as MIN_sou
,min(case when region = 'NOR' then amt else . end) as MIN_nor
You probably should have done the same for your MAX() calculations, but perhaps you didn't have any regions where the actual maximum amount was negative you didn't notice it wasn't working.
Thank you for the reply!..
Here is my data look like:
acct date region amt
1 5/21 sou 20
1 5/21 nor 21
1 6/22 sou 30
1 6/22 sou 50
2 5/21 nor 15
Output want:
min_sou min_nor
20 21
I think @Tom hit the nail on the head, if you set it to 0 if missing, that will always be the minimum. Set it to missing instead.
Thank you..It worked!
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.