topic Re: How to create a pivot sort of solution to find averages in New SAS User

How to create a pivot sort of solution to find averages

SURIM — Wed, 03 Oct 2018 13:03:50 GMT

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!

Re: How to create a pivot sort of solution to find averages

Kurt_Bremser — Wed, 03 Oct 2018 13:00:55 GMT

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;

Re: How to create a pivot sort of solution to find averages

Astounding — Wed, 03 Oct 2018 13:02:55 GMT

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?

Re: How to create a pivot sort of solution to find averages

SuryaKiran — Wed, 03 Oct 2018 14:14:41 GMT

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;

Re: How to create a pivot sort of solution to find averages

SURIM — Thu, 04 Oct 2018 09:19:22 GMT

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

Re: How to create a pivot sort of solution to find averages

SURIM — Thu, 04 Oct 2018 09:20:50 GMT

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!