topic Re: calculation in SAS Programming

calculation

Songchan — Fri, 05 Oct 2018 15:10:23 GMT

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;

Re: calculation

PaigeMiller — Fri, 05 Oct 2018 15:24:30 GMT

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-data-AKA-generate/ta-p/258712) 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?

Re: calculation

Songchan — Fri, 05 Oct 2018 15:55:29 GMT

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.

Re: calculation

Songchan — Fri, 05 Oct 2018 15:56:29 GMT

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;

Re: calculation

PaigeMiller — Fri, 05 Oct 2018 17:19:50 GMT

@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?

Re: calculation

PGStats — Fri, 05 Oct 2018 18:16:47 GMT

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.

Re: calculation

Songchan — Fri, 05 Oct 2018 18:52:50 GMT

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

Re: calculation

PGStats — Fri, 05 Oct 2018 19:15:32 GMT

Remove permno from the code and see what happens.

Re: calculation

PaigeMiller — Sat, 06 Oct 2018 11:40:10 GMT

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?