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'
![example.jpg example.jpg](https://communities.sas.com/t5/image/serverpage/image-id/53382i9C17C3E086E8596C/image-size/large?v=v2&px=999)
Many thanks in advance
Regards
George
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;
Dear ballardw,
Many thanks for your help. Unfortunately this does not work
Please see attached
I have tried with single quotes too!
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 =:
@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.
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: