How to select Max value from a group and assign value to new variable

Reply
Contributor
Posts: 22

How to select Max value from a group and assign value to new variable

Hi,

I want to select max value from group( USID,Browser,DSQ,Count). I want to create new column as TAG where i want to display value as MAX(Count) as "Max" if it have max value in that group or MIN(Count)  as "Min" if it has min value in that group.

usIdBrowserDSQcountTag
000000000000000000187FFE841E4AC5IE0230Min
000000000000000000187FFE841E4AC5IE11153

Max

000000000000000000187FFEAF933B39IE13NA
0000845F89E066661BE0816688F36603Firefo03EQ
0000845F89E066661BE0816688F36603Firefo13EQ
000C2DA9983566FD117828909924661AFirefo13
000DEAEFBE5769BE260FEFDABF48691ASafari11
000F2F72CA1F60E234952A59C91F603EChrome14
000F3ED32BAA6F4802C23BFE28AA6FF8IE07Min
000F3ED32BAA6F4802C23BFE28AA6FF8IE118Max
001304FE3010698138E801E131016929Firefo14
001799738BC96E8D34AC9C5D8FC96C17IE14
0018B1585E6169420953B4195F7E6907Firefo13
001E3740E1114B7EA77F3D1A03A5C6DEIE0135Min
001E3740E1114B7EA77F3D1A03A5C6DEIE1513Max
001E70149C846A711CC475389D9B6A95Chrome14
00207697766562D6205273A8777A62E0IE08
00207697766562D6205273A8777A62E0IE119
00211E7B2593617E054F1AC4269361F4IE13
0027CA11A7E965641B90CF3FA6F665E4IE11
0031CD5E82B666C9295CC87483A96661Firefo11
0032AA9FDC216C1531A4AE2EDD326C9CChrome057
0032AA9FDC216C1531A4AE2EDD326C9CChrome12968
0033873E1DFB6250062182111CE4627EIE013
0033873E1DFB6250062182111CE4627EIE1248
0038A355DACC68D11617A66CDBDD6875Firefo13
003A2D36EF4F6E1C3E92280BEC4F6E8CFirefo13
Super User
Posts: 19,832

Re: How to select Max value from a group and assign value to new variable

Your results don't seem consistent with the logic you've stated. Please clarify your desired results or your logic.

Contributor
Posts: 22

Re: How to select Max value from a group and assign value to new variable

Ok.

So here For example

the USID 000000000000000000187FFE841E4AC5 is having 2 records Count as 230 and 1153 , so here what i am looking for is i want to create new variable as TAG and want to assign value as Min or Max depending upon the value in that group. Hope you got my point.

Thanks.

Super User
Posts: 5,513

Re: How to select Max value from a group and assign value to new variable

Here is some more detail on what you need to clarify.

First, you state that DSQ and Count help define a group.  It looks like they don't define a group.  They are part of the information, but not part of the identifying information that defines a group.

Second, it is not clear what defines a group.  Is it the combination of USID and BROWSER, or is it UID only?

Third, what should happen in the case of ties?  Do you want multiple observations tagged as Min or Max?

Fourth, what should happen if all observations in a group have the same count?  Should they be marked as Min?  As Max?  Should it be a requirement that you have at least two different values for COUNT in order to mark anything as Min or Max?

Contributor
Posts: 22

Re: How to select Max value from a group and assign value to new variable

Posted in reply to Astounding

Ok.

1st : USID,Browser,DSQ are used for grouping and the count is taken depending upon the group(USID,Browser,DSQ).

2nd: The is defined as usid,browser and DSQ.

3rd : In case of tie then value can be N/A, or blank, i dont think i individual group we are going to get multiple values as same.

4th  : if we are gng to get all obs has same count then it can be marked as  N/A

Super User
Posts: 19,832

Re: How to select Max value from a group and assign value to new variable

You're looking for something like the following (untested):

You may need to modify the case statement to meet your requirements. This is a SAS SQL solution that will not work in native SQL probably.

proc sql;

create table want as

select usid, browser, dsq, count,  case when count=max(count) and count(usid)>1 then "MAX"

                                                            when count=min(count) and count(usid)>1  then "MIN"

                                                            else "N/A" end as Tag

from want

group by usid, browser, dsq;

Contributor
Posts: 22

Re: How to select Max value from a group and assign value to new variable

Thank you reeza, It was a helpful answer

I modified as

proc sql;

create table want as

select usid, browser_brand, dsq, count,  case when count=max(count) and count(usid)>1 and min(Count) ^= Max(Count) then "MAX "

                                                            when count=min(count) and count(usid)>1 and min(Count) ^= Max(Count)  then "MIN"

  When count=min(Count)=Max(Count) and count(usid)>1 Then 'EQ'

                                                            else "N/A"  end as Tag

from temp

group by usid, browser_brand;

Quit;

Ask a Question
Discussion stats
  • 6 replies
  • 3334 views
  • 4 likes
  • 3 in conversation