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

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
,

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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?

--
Paige Miller
Tom
Super User Tom
Super User

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.

Kalai2008
Pyrite | Level 9

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

Reeza
Super User

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.

Kalai2008
Pyrite | Level 9

Thank you..It worked!

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!

How to Concatenate Values

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.

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
  • 2204 views
  • 2 likes
  • 4 in conversation