Help using Base SAS procedures

How to calculate average

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 125
Accepted Solution

How to calculate average

Hi ,

 

I have a dataset which has item id , item type , some factors and i want to calculate average by item id and item type

 

Item id Item_type APPLE_T1 ORANGE_T1
123 BOTH 2 2
123 BOTH 2 2
123 BOTH 2 2
123 APPLE 1.6 0
123 ORANGE 0 1.3
456 BOTH 2 2
456 BOTH 2 2
456 APPLE 1.6 0

 

So while calculating average of APPLE_T1 and Orange_t1 we should consider by itemid and itemtype we should note that Item_type=BOTH means (its applicable for both APPLES AND ORANGES )

 

So after we create average of those by ids the table should look like below

 

So for item id 123 - APPLE_T1 1.9 is  (2+2+2+1.6)/4  and for orange_t1 1.825 is  (2+2+2+1.3)/4

and for item 456 - APPLE_T1 is (2+2+1.6)/3  and for orange_t1 = 2+2 /2

Item id APPLE_T1 ORANGE_T1
123 1.9 1.825
456 1.86 2

 

 

CAn anyone please help on how do i need to do this type of averaging

 

Thank you


Accepted Solutions
Solution
‎09-13-2017 07:42 PM
PROC Star
Posts: 1,805

Re: How to calculate average

Posted in reply to chennupriya

data have;
infile datalines truncover;
input Item Item_type $ APPLE_T1 ORANGE_T1;
datalines;
123 BOTH 2 2
123 BOTH 2 2
123 BOTH 2 2
123 APPLE 1.6 0
123 ORANGE 0 1.3
456 BOTH 2 2
456 BOTH 2 2
456 APPLE 1.6 0
;

 

proc sql;
create table want as
select Item, mean(case when APPLE_T1=0 then . else APPLE_T1 end ) as m1, mean(case when ORANGE_T1=0 then . else ORANGE_T1 end ) as m2
from have
group by item;
quit;

 

Also try proc means, summary etc. Search and copypaste code from internet

View solution in original post


All Replies
Solution
‎09-13-2017 07:42 PM
PROC Star
Posts: 1,805

Re: How to calculate average

Posted in reply to chennupriya

data have;
infile datalines truncover;
input Item Item_type $ APPLE_T1 ORANGE_T1;
datalines;
123 BOTH 2 2
123 BOTH 2 2
123 BOTH 2 2
123 APPLE 1.6 0
123 ORANGE 0 1.3
456 BOTH 2 2
456 BOTH 2 2
456 APPLE 1.6 0
;

 

proc sql;
create table want as
select Item, mean(case when APPLE_T1=0 then . else APPLE_T1 end ) as m1, mean(case when ORANGE_T1=0 then . else ORANGE_T1 end ) as m2
from have
group by item;
quit;

 

Also try proc means, summary etc. Search and copypaste code from internet

Frequent Contributor
Posts: 125

Re: How to calculate average

Posted in reply to novinosrin

It Worked

 

Thank you

Super User
Super User
Posts: 8,112

Re: How to calculate average

Posted in reply to novinosrin

So basically you are ignoring the ITEM_TYPE variable and assuming that a value of zero is a missing value instead?

What if zero is a valid value?

create table want as
  select Item
       , mean(case when item_type ne 'ORANGE' then APPLE_T1 else . end) as mean_apple
       , mean(case when item_type ne 'APPLE' then ORANGE_T1 else . end) as mean_orange
  from have
  group by item
  ;
quit;
Super User
Posts: 23,724

Re: How to calculate average

Posted in reply to chennupriya

 

A multilabel format may also be an option, here's how it may work. You'd have to check if its any easier, but I can't seem to think this one through for some reason.

 

data have;
infile datalines truncover;
input Item Item_type $ APPLE_T1 ORANGE_T1;
datalines;
123 BOTH 2 2
123 BOTH 2 2
123 BOTH 2 2
123 APPLE 1.6 0
123 ORANGE 0 1.3
456 BOTH 2 2
456 BOTH 2 2
456 APPLE 1.6 0
;
run;



title 'Fruit Summary by Item Type';
proc means data=HAVE n mean sum maxdec=2;
class item item_type /mlf;
format item_type $fruit.;
var apple_t1 orange_t1;
ods output summary=want;
run;

 

 

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 636 views
  • 1 like
  • 4 in conversation