1. How to filter data based on how long it will last from the transaction date?
issue date transaction date ticker transaction shares
8/7/2011 8/13/2011 form 100
8/7/2011 12/13/2011 form 200
8/7/2011 6/2/2012 form 300
9/24/2011 10/15/2011 abc 150
9/24/2011 9/17/2012 abc 175
I wanna filter transaction date only for the date during 180 days/ 6 month after issue date
2. how to create dummy variable based on the date before specific date? the dummy variable should be 1 for outsider director appointed before CEO appointed, 0 otherwise
for example:
Director Name CEO Outside Director Year Service Began Firm Name
AA 1 0 1999 ABC
AB 0 1 2000 ABC
AC 0 1 1996 ABC
AD 0 1 2000 AAC
AE 1 0 2002 AAC
Thanks in advance
proc sql;
select * from have
where intck('month',issue_date,transaction_date)<=6;
quit;
proc sql;
select * from have
where intck('month',issue_date,transaction_date)<=6;
quit;
Alternatively, if you want to filter by days then replace month from stat@sas code like below
intck('day',issue_date,transaction_date)<=180
with regard to the second query , i created a variable CEO appointed date to get the expected output. i compared the CEO appointed date with the year service began date to generate the dummy variables dir and ceo.
data have;
input Director_Name $ Year_Service_Began Firm_Name$ CEO_appointed;
datalines;
AA 1999 ABC 1998
AB 2000 ABC 2005
AC 1996 ABC 2005
AD 2000 AAC 2005
AE 2002 AAC 2001
;
run;
data want;
set have;
if Year_Service_Began < CEO_appointed then dir=1;
else if Year_Service_Began > CEO_appointed then dir=0;
if CEO_appointed < Year_Service_Began then ceo=1;
else if CEO_appointed > Year_Service_Began then ceo=0;
run;
Thanks,
Jag
Thanks for the alternative way Jag..
in respect to the second problem..how to create CEO appointed date as you mentioned above regarding my dataset? I've tried to do so by separating year service began that means when the CEO and outside director were appointed respectively. However, the new dataset I got as follows:
Director Name CEO Outside Director Year Service Began Firm Name CEO Year Outside Director Year
AA 1 0 1999 ABC 1999 -
AB 0 1 2000 ABC - 2000
AC 0 1 1996 ABC - 1996
AD 0 1 2000 AAC - 2000
AE 1 0 2002 AAC 2002 -
if so, I can't compare the CEO year and outside director year to create dummy variable I want.
Thanks in advance
Hi Acil, sorry if i confused you, i created the CEO appointed date as an example, just to convey that if you have something similar in your data then you can use it to compare with the year service began and then derive the CEO and Outside Director dummy variables.
May be i did not understand your question, in the earliest example you have showed the CEO and Outside Director dummy variables which you wanted to create right.
So i used CEO appointed date as example. you have a similar variable in your data to compare and get the dummy variables.
Dear Jag,
what I wanna create is dummy variable for outside director appointed before appointed CEO. Year Service Began is the year for when both CEO and outside director were appointed. Then if I need to create CEO appointed date, it should be retrieved from Year Service Began then applied to all outside directors in the same firms. For CEO and Outside Director dummy are just for identifying the position those in the Director Name.
Thanks in advance
if i understood , could you please try
data have;
input Director_Name $ CEO Outside_Director Year_Service_Began Firm_Name$ ;
datalines;
AA 1 0 1999 ABC
AB 0 1 2000 ABC
AC 0 1 1996 ABC
AD 0 1 2000 AAC
AE 1 0 2002 AAC
;
run;
proc sort data=have;
by Firm_Name descending CEO Outside_Director ;
run;
data want;
set have;
retain CEO_date;
by Firm_Name descending CEO Outside_Director ;
if first.Firm_Name then CEO_date= Year_Service_Began;
if ceo=1 then CEO_date_=.;
else CEO_date_=CEO_date;
drop CEO_date;
run;
Thanks,
Jag
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.