topic Re: Filter data based on how long it last in SAS Enterprise Guide

Filter data based on how long it last

acil — Sat, 08 Nov 2014 15:33:57 GMT

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

Re: Filter data based on how long it last

stat_sas — Sat, 08 Nov 2014 20:15:05 GMT

proc sql;

select * from have

where intck('month',issue_date,transaction_date)<=6;

quit;

Re: Filter data based on how long it last

Jagadishkatam — Sun, 09 Nov 2014 08:26:50 GMT

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

Re: Filter data based on how long it last

acil — Sun, 09 Nov 2014 12:36:50 GMT

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

Re: Filter data based on how long it last

Jagadishkatam — Sun, 09 Nov 2014 13:41:07 GMT

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.

Re: Filter data based on how long it last

acil — Sun, 09 Nov 2014 13:58:29 GMT

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

Re: Filter data based on how long it last

Jagadishkatam — Sun, 09 Nov 2014 14:34:27 GMT

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