topic Re: Calculating returns from prices in SAS Programming

Calculating returns from prices

georgel — Tue, 09 Mar 2021 19:51:41 GMT

Hi all

Please need to simplify the following code or alternative more efficient way to do it:

proc sort data=example ;
     by ISIN datadate;
run;

data example ;
     set example;
	 lag_ISIN=lag(ISIN);
	 lag_PRCCD=lag(PRCCD);
	 If lag_ISIN=ISIN then RET=PRCCD/lag_PRCCD-1;else RET=.;
run;

Re: Calculating returns from prices

Reeza — Tue, 09 Mar 2021 20:15:06 GMT

data example ;
     set example;
by ISIN;
	
RET=PRCCD/lag_PRCCD - 1;
if first.ISIN then RET=.;
format ret percent12.1;
run;

There really isn't much to simplify here that I can see, you still end up with roughly the same lines of code. You could possibly remove one by using IFN() but I don't think you gain any efficiency doing so.

@georgel wrote:

Hi all

Please need to simplify the following code or alternative more efficient way to do it:
proc sort data=example ;
     by ISIN datadate;
run;

data example ;
     set example;
	 lag_ISIN=lag(ISIN);
	 lag_PRCCD=lag(PRCCD);
	 If lag_ISIN=ISIN then RET=PRCCD/lag_PRCCD-1;else RET=.;
run;

Re: Calculating returns from prices

Phil_NZ — Tue, 09 Mar 2021 21:08:29 GMT

@georgel

It seems that you are dealing with data from CRSP or Compustat in Finance

I agree with solution from @Reeza , just want to highlight that you should calculate the return first then set the if later like this answer (because the way "if" plays quite tricky).

You may receive a lot of notes that "Missing ..." in your log, but it makes sense and (maybe) safe because there should be some missing obs somewhere in your dataset. If you want to be over this note, the norm is to set the full condition if you want to clarify any NOTE.

For example:

lag_prccd=lag(prccd);

may cause the note" missing..."

but

lag_prccd=ifn(prccd<=0,., lag(prccd));

won't result in such a note.

Remember Compustat put the average of Bid/Ask spread as closing price (prccd in your case) and missing prccd will be set as 0 rather than ".", so be careful when dealing with such a variable in your dataset

Warm regards,

Phil.