Desktop productivity for business analysts and programmers

Query to return 1 instance for each id

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Query to return 1 instance for each id

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_IDStorePurchases
132
154
143
253
275
265
338
352
341
452
473
461
532
553
541
651
672
665
734
753
745
852
873
861

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


Accepted Solutions
Solution
‎07-16-2013 08:08 AM
Super Contributor
Posts: 339

Re: Query to return 1 instance for each id

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.

View solution in original post


All Replies
Super Contributor
Posts: 282

Re: Query to return 1 instance for each id

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.

Solution
‎07-16-2013 08:08 AM
Super Contributor
Posts: 339

Re: Query to return 1 instance for each id

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.

Occasional Contributor
Posts: 14

Re: Query to return 1 instance for each id

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 Smiley Wink

Thanks again mate !

Occasional Contributor
Posts: 14

Re: Query to return 1 instance for each id

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

Contributor
Posts: 38

Re: Query to return 1 instance for each id

Hi

Proc sql;

select Client_ID,Store,max(Purchases)as MaxPurchases

from yourtable

group by Client_ID,Store;

quit;

Fred

Occasional Contributor
Posts: 14

Re: Query to return 1 instance for each id

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 282 views
  • 0 likes
  • 4 in conversation