calculation

[Songchan]

Hello guys,

I want to use the data (please see the photo) to calculate a factor, the formula is imomf=1/2*(bw+sw)-1/2*(bl+sl), but the thing is i need bw, sw, bl and sl 's corresponding imom statistics to calculate imomf, and i need it sorted by date, so that on each date i get one imomf.

Could somebody help me with that?

Regards.

data temp01; set mylib.rankimom01;
    keep permno date imom kind;
run;

proc sort data=temp01;by date;run;
data temp02; set temp01;
   imomf=1/2*(bw+sw)-1/2*(bl+sl);
run;

[PaigeMiller]

First, you need to post your data as SAS code (see this document for instructions: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...) rather than as a photo, which we can't use.

Secondly, since you don't have variables bl, sl, bw and sw, your code won't work. Can you tell us where these variables come from? How would YOU create these variables?

[Songchan]

Hi,

thanks for reply, the photo is just an example, I only want you to know what it looks like, and there are four kinds of data in "kind" column, bw, bl, sw, sl. I didn't show them all in the photo.

[PaigeMiller]

---

@Songchan wrote:

Hi,

thanks for reply, the photo is just an example, I only want you to know what it looks like, and there are four kinds of data in "kind" column, bw, bl, sw, sl. I didn't show them all in the photo.

---

Again, we need to see a reasonable portion of the data so we can understand what to do. If your example only shows bl as a character string and not any of the others, how are we supposed to make progress? If there are four different kinds of data in the column "kind", that should have been clearly stated in the original message.

Now please explain what we should do with PERMNO? What is the aggregation method for multiple observations in a given value of DATE1?

[Songchan]

data temp03; set temp02;
   if rank=1 and size='b' then kind='bl';
   if rank=1 and size='s' then kind='sl';
   if rank=10 and size='b' then kind='bw';
   if rank=10 and size='s' then kind='sw';
run;

[PGStats]

It looks like you are only missing a transposition step. Perhaps:

data temp01; set mylib.rankimom01;
    keep permno date imom kind;
run;

proc sort data=temp01;by permno date;run;

proc transpose data=temp01 out=temp02;
by permno date;
var imom;
id kind;
run;

data temp03; set temp02;
   imomf=1/2*(bw+sw)-1/2*(bl+sl);
run;

Not sure about the role of permno. Untested.

[Songchan]

thanks for replying, but there are still no value in imomf column. permno doesnt matter.

[PGStats]

Remove permno from the code and see what happens.

[PaigeMiller]

You still haven't described what we should do about the multiple values with the same date. What should we do about this? Average the values with the same date? Sum the values with the same date?