BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

Hello

I have data of transactions (shopping transactions).

I want to create the following summary table:

For each subject and metric need to calculate mean,median,min,max,q90,q75,std

There are 4 metrics:

1-Number of transactions per month per customer 

2-Sum of  transactions' amounts per month per customer 

3-Number of transactions per month for all customers

4-Sum of  transactions' amounts per month for all customers

 

What is the way to do it please?

Ronein_0-1638279850662.png

 

 

 

Data have;
input ID YYMM subject $   amount;
cards;
1 2101 A 10
1 2101 A 20
1 2101 A 30
1 2101 A 50
1 2101 A 20
1 2101 B 80
1 2101 B 10
1 2101 B 30
1 2101 B 49
1 2101 B 20
1 2101 C 30
1 2102 C 19
1 2102 C 90
1 2102 C 70
1 2102 C 34
1 2102 C 34
1 2102 C 53
1 2102 C 87
1 2102 C 76
1 2102 C 98
1 2102 C 19
1 2102 C 23
2 2101 A 53
2 2101 A 98
2 2101 A 3
2 2101 B 53
2 2101 C 4
2 2101 C 56
2 2101 C 97
2 2101 C 35
2 2101 C 83
2 2101 C 64
2 2101 C 24
2 2102 C 65
2 2102 C 45
2 2102 C 54
2 2102 C 76
2 2102 C 34
2 2102 C 65
2 2102 C 86
2 2102 C 56
2 2102 C 45
3 2101 C 40
3 2102 C 56
3 2102 C 76
;
Run;

 

 

6 REPLIES 6
AMSAS
SAS Super FREQ

Have you looked at the documentation on PROC UNIVARIATE?

Ronein
Meteorite | Level 14

How can I add mean ,std,Q75 to the calculations?

Data have;
input ID YYMM subject $   amount;
cards;
1 2101 A 10
1 2101 A 20
1 2101 A 30
1 2101 A 50
1 2101 A 20
1 2101 B 80
1 2101 B 10
1 2101 B 30
1 2101 B 49
1 2101 B 20
1 2101 C 30
1 2102 C 19
1 2102 C 90
1 2102 C 70
1 2102 C 34
1 2102 C 34
1 2102 C 53
1 2102 C 87
1 2102 C 76
1 2102 C 98
1 2102 C 19
1 2102 C 23
2 2101 A 53
2 2101 A 98
2 2101 A 3
2 2101 B 53
2 2101 C 4
2 2101 C 56
2 2101 C 97
2 2101 C 35
2 2101 C 83
2 2101 C 64
2 2101 C 24
2 2102 C 65
2 2102 C 45
2 2102 C 54
2 2102 C 76
2 2102 C 34
2 2102 C 65
2 2102 C 86
2 2102 C 56
2 2102 C 45
3 2101 C 40
3 2102 C 56
3 2102 C 76
;
Run;

proc sql;
create table Data_per_ID_Month_Subject as
select  ID,YYMM,subject,
       count(*) as nr_Trans,
	   sum(amount) as amount
from have
group by  ID,YYMM,subject
;
quit;


proc univariate data=Data_per_ID_Month_Subject noprint; ;
var nr_Trans;
class subject;
output out = wanted
pctlpts = 10 to 100 by 10 pctlpre = nr_Trans_p_;
Run;

sbxkoenk
SAS Super FREQ

Hello,

 

With Q75 I guess you mean Q3 = P75?

You can do something like this and add stddev as well :

proc univariate data=Belts noprint;
   var Strength Width Height;
   output out=Pctls pctlpts  = 5 10 20 25 30 40 50 60 75 80 90 95
                    pctlpre  = Strength Width Height
                    pctlname =  pctl5 pctl10 pctl20 pctl25 pctl30 pctl40 
                               pctl50 pctl60 pctl75 pctl80 pctl90 pctl95 ;
run;

Koen

Ronein
Meteorite | Level 14
Thanks
What about adding mean and std ?
I don't see it in your code
Ronein
Meteorite | Level 14
I wanted to learn do it via proc tabulate because I want a structure of two way table where in first dimension have : var_name, metric and in second dimension has subject
sbxkoenk
SAS Super FREQ

Here's a two-way table :

proc univariate data=sashelp.shoes noprint;
   var Sales Inventory Returns;
   output out=Pctls pctlpts  = 5 10 20 25 30 40 50 60 75 80 90 95
                    pctlpre  = Sales_ Inventory_ Returns_ 
                    pctlname =  pctl5 pctl10 pctl20 pctl25 pctl30 pctl40 
                               pctl50 pctl60 pctl75 pctl80 pctl90 pctl95
					mean=Sales_mean Inventory_mean Returns_mean
					std =Sales_std  Inventory_std  Returns_std ;
run;

PROC TRANSPOSE data=Pctls out=Pctls_trp;
run;

data Pctls_trp;
 set Pctls_trp;
 _NAME_=scan(_NAME_,1,'_');
run;

proc sort data=Pctls_trp;
 by _NAME_;
run;

proc transpose data=Pctls_trp out=Pctls_trp_trp;
 by _NAME_;
 id _LABEL_;
 var col1;
run;
/* end of program */

Koen

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1298 views
  • 1 like
  • 3 in conversation