topic Re: Keep only numeric returns from CRSP using SQL in SAS Programming

Keep only numeric returns from CRSP using SQL

georgel — Mon, 11 Jan 2021 14:45:50 GMT

Hi all,

I would like to add a condition in my sql to drop returns appear in CRSP as 'C':

PROC SQL;
       create table dsf as
       select a.* , b.date,  b.RET as RET
       from work.Benchmarks2 as a
	   left join temp  as b
	   on a.cusip=b.cusip and a.Conditional_Exercise_Date<b.date and b.date<=b.date+756-count_temp;
quit;

/*The resulting table is:*/
Also it is stated that returns in CRSP are numeric in format but in the first row appears the letter 'C'

Many thanks in advance

Regards

George

Re: Keep only numeric returns from CRSP using SQL

ballardw — Mon, 11 Jan 2021 14:58:51 GMT

I am guessing that the variable RET holds your "returns" since you do not state so.

PROC SQL; create table dsf as select a.* , b.date, b.RET as RET from work.Benchmarks2 as a left join ( select * from temp where not ret=:'C' ) as b on a.cusip=b.cusip and a.Conditional_Exercise_Date<b.date and b.date<=b.date+756-count_temp; quit;

Re: Keep only numeric returns from CRSP using SQL

georgel — Mon, 11 Jan 2021 15:36:11 GMT

Dear ballardw,

Many thanks for your help. Unfortunately this does not work

Please see attached

I have tried with single quotes too!

Re: Keep only numeric returns from CRSP using SQL

ballardw — Mon, 11 Jan 2021 16:27:37 GMT

SAS has enough extension I hoped the "begins with" equality, the =: would work.

where not substr(ret,1,1) = 'C'

should work instead of the =:

Re: Keep only numeric returns from CRSP using SQL

Tom — Mon, 11 Jan 2021 16:32:13 GMT

@ballardw wrote:

SAS has enough extension I hoped the "begins with" equality, the =: would work.

where not substr(ret,1,1) = 'C'

should work instead of the =:

In SQL you can use the EQT operator.

Re: Keep only numeric returns from CRSP using SQL

georgel — Mon, 11 Jan 2021 17:57:27 GMT

Here it is the solution with a little modification!


PROC SQL;
       create table dsf as
       select a.* , b.date,  b.RET as RET
       from work.Benchmarks2 as a
	   left join ( select * from temp
                       where ret+0^=. )  as b
	   on a.cusip=b.cusip and a.Conditional_Exercise_Date<b.date and b.date<=b.date+756-count_temp;
quit;

Thanks