topic Re: Query to return 1 instance for each id in SAS Enterprise Guide

Query to return 1 instance for each id

M_A_C — Tue, 16 Jul 2013 11:33:52 GMT

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?!

Re: Query to return 1 instance for each id

Amir — Tue, 16 Jul 2013 11:58:07 GMT

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.

Re: Query to return 1 instance for each id

fred_major — Tue, 16 Jul 2013 11:59:53 GMT

Proc sql;

select Client_ID,Store,max(Purchases)as MaxPurchases

from yourtable

group by Client_ID,Store;

quit;

Fred

Re: Query to return 1 instance for each id

Vince28_Statcan — Tue, 16 Jul 2013 12:08:36 GMT

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.

Re: Query to return 1 instance for each id

M_A_C — Tue, 16 Jul 2013 12:30:09 GMT

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 !

Re: Query to return 1 instance for each id

M_A_C — Tue, 16 Jul 2013 12:31:02 GMT

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

Re: Query to return 1 instance for each id

M_A_C — Tue, 16 Jul 2013 12:32:13 GMT

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.