Keep only numeric returns from CRSP using SQL

[georgel]

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

[ballardw]

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;

[georgel]

Dear ballardw,

Many thanks for your help. Unfortunately this does not work

Please see attached

I have tried with single quotes too!

[ballardw]

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 =:

[Tom]

---

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

[georgel]

 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