topic mean,median,min,max,q90,q75,std with different metrics in SAS Programming

mean,median,min,max,q90,q75,std with different metrics

Ronein — Tue, 30 Nov 2021 13:46:53 GMT

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?

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;

Re: mean,median,min,max,q90,q75,std with different metrics

AMSAS — Tue, 30 Nov 2021 14:42:06 GMT

Have you looked at the documentation on PROC UNIVARIATE?

Re: mean,median,min,max,q90,q75,std with different metrics

Ronein — Tue, 30 Nov 2021 15:18:13 GMT

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;

Re: mean,median,min,max,q90,q75,std with different metrics

sbxkoenk — Tue, 30 Nov 2021 15:35:57 GMT

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

Re: mean,median,min,max,q90,q75,std with different metrics

Ronein — Tue, 30 Nov 2021 15:42:46 GMT

Thanks
What about adding mean and std ?
I don't see it in your code

Re: mean,median,min,max,q90,q75,std with different metrics

Ronein — Tue, 30 Nov 2021 15:45:01 GMT

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

Re: mean,median,min,max,q90,q75,std with different metrics

sbxkoenk — Tue, 30 Nov 2021 15:57:15 GMT

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