BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chennupriya
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

4 REPLIES 4
novinosrin
Tourmaline | Level 20

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

chennupriya
Quartz | Level 8

It Worked

 

Thank you

Tom
Super User Tom
Super User

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;
Reeza
Super User

 

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;

 

 

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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