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

Dear Community Members,

I have a dataset that looks like the following:

DATA return;

INPUT portid firmid myear return;

DATALINES;

1 100 200301  2.11

1 120 200301  3.12

1 130 200301  2.13

1 140 200301  3.67

1 100 200302  5.23

1 120 200302  6.81

1 130 200302  2.55

1 140 200302  4.21

1 150 200302  3.21

2 300 200301  1.11

2 320 200301  0.12

2 330 200301 -2.13

2 340 200301 -1.67

2 300 200302  4.23

2 320 200302  3.81

2 330 200302  1.55

2 340 200302  2.21

2 350 200302  4.23

;

run;

My objective is to calculate average returns for each portid and myear (i.e. average "return" in each portid-myear pair). Next, match each firmid in a given portid and myear with  the corresponding average returns. Normally, I can use "proc means" by "portid myear", but there is a restriction

such that a company's return should be excluded from the corresponding average return. For example,

for portid=1, myear=200301, firmid=100, the corresponding average return=(3.12+2.13+3.67)/3=2.9733333

for portid=1, myear=200301, firmid=120, the corresponding average return=(2.11+2.13+3.67)/3=2.6366666

for portid=2, myear=200302, firmid=350, the corresponding average return=(4.23+3.81+1.55+2.21)/4=2.95

I would really appreciate your help with this problem.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

Probably many ways to achieve what you want.  E.g.:

proc transpose data=return out=need (drop=_name_);

  var return;

  id firmid;

  by portid myear;

run;

data need;

  merge return need;

  by portid myear;

run;

data want (drop=i _:);

  set need;

  array returns(*) _100--_350;

  do i=1 to dim(returns);

    if input(substr(vname(returns(i)),2),8.) eq firmid then

      call missing(returns(i));

  end;

  average=mean(of returns(*));

run;

View solution in original post

6 REPLIES 6
art297
Opal | Level 21

Probably many ways to achieve what you want.  E.g.:

proc transpose data=return out=need (drop=_name_);

  var return;

  id firmid;

  by portid myear;

run;

data need;

  merge return need;

  by portid myear;

run;

data want (drop=i _:);

  set need;

  array returns(*) _100--_350;

  do i=1 to dim(returns);

    if input(substr(vname(returns(i)),2),8.) eq firmid then

      call missing(returns(i));

  end;

  average=mean(of returns(*));

run;

PGStats
Opal | Level 21

There is a simple SQL solution :

proc sql;

create table want as

select a.*, mean(b.return) as meanEx

from return as a inner join return as b

on a.portId = b.portId and a.myear=b.myear and b.firmId ne a.firmId

group by a.portId, a.mYear, a.firmId, a.return;

select * from want;

quit;

PG

PG
Linlin
Lapis Lazuli | Level 10

Hi PG,

I don't understand your code. How did you get the average meanEX?  Thanks!

PGStats
Opal | Level 21

Hi Linlin,

OK, I retract the words simple solution. For each obs in RETURN, take all obs in RETURN with the same portId and mYear, except the one with the same formId. Take the average for each obs.

I guess the best way to understand is to look at the result from

proc sql;

select a.*, b.return as otherReturn

from return as a inner join return as b

on a.portId = b.portId and a.myear=b.myear and b.firmId ne a.firmId;

PG

PG
Linlin
Lapis Lazuli | Level 10

Thank you PGSmiley Happy!

finans_sas
Quartz | Level 8

Thank you so much for your help and clarifying question. These two solutions are really brilliant and practical.

Best,

SK

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 6 replies
  • 1052 views
  • 12 likes
  • 4 in conversation