Query to return 1 instance for each id

[M_A_C]

Hey guys, thanks for the time,

So not to confuse you, i will try to explain my issue.

I have a table that contains information about clients and i would like to choose the store with maximum purchases for each client

Client_ID	Store	Purchases
1	3	2
1	5	4
1	4	3
2	5	3
2	7	5
2	6	5
3	3	8
3	5	2
3	4	1
4	5	2
4	7	3
4	6	1
5	3	2
5	5	3
5	4	1
6	5	1
6	7	2
6	6	5
7	3	4
7	5	3
7	4	5
8	5	2
8	7	3
8	6	1

So, for the example above, i need to choose the store with the maximum purchases for my client.

I don't think i can use "top with ties" which seems to be the solution if i were using T-SQL but since my data are stored on SAS EG, i cannot do so.

Ideas, please?!

[Amir]

Hi,

To carry this out with a data step look into reading the data by Client_ID and using the retain statement to hold values of the Store and Purchases.

if this is the highest value for Purchases in this by group then save the values of Store and Purchases.

At the end of the by group you can output the Client_ID and the Store and Purchases values you have retained to your output data set.

I hope that gives you some clues.

Regards,

Amir.

[Vince28_Statcan · Accepted solution]

Hi,

Fred's solution will return one record for each client_id, store pair and not one record per client_id. I believe you wanted a single store returned per client so here are 2 solutions viable. The sort+data step will return only a single record. The proc sql will return all stores that share the same maximum purchase assuming you would want more than one record if there was a tie for the maximum

data temp;

     input clientid store purchases;

     datalines;

1 1 100

1 2 155

1 3 155

2 3 222

2 2 234

2 4 111

;

run;

/* Soln 1, single return even when there are ties */

proc sort data=temp;

     by clientid purchases;

run;

data temp2;

     set temp;

     by clientid;

     if last.clientid then output;

run;

/* end of soln1 */

/* soln2, multiple returns when ties occur */

proc sql;

     select clientid, store, purchases

     from (

          select clientid, store, purchases, max(purchases) as maxpurchases

          from temp

          group by clientid

          )

     where purchases=maxpurchases;

quit;

Hope this helps.

[M_A_C]

Thanks Vince,

Soln1 was exactly what i was looking for.

Gotta automate a whole process to get to the study of product affinity so has to start with this

Thanks again mate !

[M_A_C]

Thanks Amir, gave me clues as you said vince ws kind enough to give me the solution lol

[fred_major]

Hi

Proc sql;

select Client_ID,Store,max(Purchases)as MaxPurchases

from yourtable

group by Client_ID,Store;

quit;

Fred

[M_A_C]

Thanks fred for the response.

The script you wrote does return a result for each (Client_Id, Store) Combination which isn't what i am looking for but the answer vince gave was just it.