Help using Base SAS procedures

Transactions per client

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 117
Accepted Solution

Transactions per client

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 | A1

001 | A2

001 | A3

002 | A4

002 | A5

003 | A6

003 | A6

Result would be:

Client 001 has 3 unique transactions

Client 002 has 2 unique transactions

Client 003 has 1 unique transactions

Thanks!


Accepted Solutions
Solution
‎08-02-2013 01:50 AM
Regular Contributor
Posts: 195

Re: Transactions per client

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

View solution in original post


All Replies
Contributor
Posts: 69

Re: Transactions per client

data a;

input id $ Trans $;

cards;

...

Proc SQL ;

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

Contributor
Posts: 64

Re: Transactions per client

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;

Solution
‎08-02-2013 01:50 AM
Regular Contributor
Posts: 195

Re: Transactions per client

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 218 views
  • 4 likes
  • 4 in conversation