How to create a pivot sort of solution to find averages

[SURIM]

I can't figure out how to do this. I need to calculate, per country:

- average order value per OrderID.

- average number of orders per ID

- average items (orderrows) per orderID

Problem is, there is one row for every item in an order (10000, 10001...) and therefor several identical orderid:s in the column.

I have tried Proc means and Proc tabulate but the multiple rows make it difficult.

COUNTRY            ID                            OrderID                 Orderrow            Price

FR                           100000008003    00027006             10000                    4 €

DE                           100000008008    01081101             10003                    34 €

DE                           100000008008    01081101             10000                    34 €

DE                           100000008008    01081101             10002                    21 €

DE                           100000008008    01694021             10000                    8 €

DE                           100000008008    01081101             10001                    50 €

DE                           100000008008    00027014             10000                    37 €

DE                           100000008008    02141491             10000                    15 €

DE                           100000008008    03724561             10000                   68 €

DK                          100000011008    00036013             10000                    4 €

DK                          100000011008    00036013             10001                    36 €

US                          100000011009    00036015             10000                    67 €

US                          100000011009    00036015             10001                    17 €

FR                           100000012002    00064002             10001                    71 €

FR                           100000012002    00064002             10000                    71 €

I´m on SAS Studio,

Thanks!

[Kurt_Bremser]

Please post your example data in a readily usable form (data step with datalines), not as pictures. We want to be able to recreate your dataset with simple copy-paste and submit, without having to do a lot of typing.

Such a step can look like this:

data have;
input country :$2. ID :$12. orderid :$8. orderrow :$5. price;
format price dollar8.;
cards;
FR 100000008003 00027006 10000 4
;
run;

[Astounding]

You might need to write a separate (short) program for each question.

Just to nudge you in the right direction, do you know of any way to get the total order value for each OrderID?

[SURIM]

Hi! I get what you are saying, perhaps it would be easier to split it. I'm gonna try to do a proc mean + a proc tabulate with just one question at a time to be able to reuse the code for similar purposes. Thank you!

[SuryaKiran · Accepted solution]

You might need to develop multiple queries and then append them to get the result you need. 

Something like this:

data have;
infile datalines dlm=',' dsd missover;
input COUNTRY:$8. ID :$25. OrderID :$25. Orderrow :$25. Price;
datalines;
FR,100000008003,00027006,10000,4
DE,100000008008,01081101,10003,34
DE,100000008008,01081101,10000,34
DE,100000008008,01081101,10002,21
DE,100000008008,01694021,10000,8
DE,100000008008,01081101,10001,50
DE,100000008008,00027014,10000,37
DE,100000008008,02141491,10000,15
DE,100000008008,03724561,10000,68
DK,100000011008,00036013,10000,4
DK,100000011008,00036013,10001,36
US,100000011009,00036015,10000,67
US,100000011009,00036015,10001,17
FR,100000012002,00064002,10001,71
FR,100000012002,00064002,10000,71
;
run;

/*
- average order value per OrderID.
- average number of orders per ID
- average items (orderrows) per orderID
*/

proc sql;
select "Avg Orders value/OrderID" as Des,Avg(Sum_Price) as Average
from(select Sum(Price) as Sum_price
		from have
		group by OrderID)
Union 
select "Avg Orders/ID" as Des,Avg(count) as Average
from (select count(*) as Count
		from have
		group by ID)
Union
select "Avg Items/OrderID" as Des,Avg(count) as Average
from (select count(*) as Count
		from have
		group by OrderID)
;
quit;

[SURIM]

Wow! You made it work! Thank you so much.