topic Re: Transactions per client in SAS Procedures

Transactions per client

vomer — Thu, 01 Aug 2013 18:44:18 GMT

Hi there,

I am wondering what the best way is to get this kind of analysis?

Lets assume if my data looks like:

ClientID | Transaction

001 | A1

001 | A2

001 | A3

002 | A4

002 | A5

003 | A6

Result would be:

Client 001 has 3 unique transactions

Client 002 has 2 unique transactions

Client 003 has 1 unique transactions

Thanks!

Re: Transactions per client

GPatel — Thu, 01 Aug 2013 19:27:08 GMT

data a;

input id $ Trans $;

cards;

...

Proc SQL ;

Select id, count(Distinct(Trans)) as UnqTrans from A Group by ID; Quit;

Re: Transactions per client

DR_Majeti — Fri, 02 Aug 2013 03:12:20 GMT

Hi Vomer,

This is another process by Proc Freq through sorting

Data want;
input id trans $;
datalines;
001 A1
001 A1
001 A2
001 A3
002 A4
002 A5
003 A6
003 A6
;
proc sort data=want nodup;
By id trans;
run;
Proc freq data=want;
table id / nopct nocum; Run;

run;

Re: Transactions per client

UrvishShah — Fri, 02 Aug 2013 05:50:44 GMT

proc sql;

create table want as

select client_id,count(distinct transactions) as tot_trans

from have

group by client_id

order by client_id;

quit;

If your data is very big then the above code will take much time as i have included count(distinct transactions)...so SQL processor has to make two passes through the data in order to eliminate the duplicates...

In that case, just try with following one...

proc sql;

create table want as

select distinct client_id, transactions

from have;

quit;

proc freq data = want noprint;

table client_id / out = want(drop = percent);

run;

Choice is yours based on the size of data...

-Urvish