Desktop productivity for business analysts and programmers

Filter data based on how long it last

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Filter data based on how long it last

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


Accepted Solutions
Solution
‎11-08-2014 03:15 PM
Trusted Advisor
Posts: 1,228

Re: Filter data based on how long it last

proc sql;

select * from have

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

quit;

View solution in original post


All Replies
Solution
‎11-08-2014 03:15 PM
Trusted Advisor
Posts: 1,228

Re: Filter data based on how long it last

proc sql;

select * from have

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

quit;

Trusted Advisor
Posts: 1,137

Re: Filter data based on how long it last

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,
Jag
Occasional Contributor
Posts: 8

Re: Filter data based on how long it last

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

Trusted Advisor
Posts: 1,137

Re: Filter data based on how long it last

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.

Thanks,
Jag
Occasional Contributor
Posts: 8

Re: Filter data based on how long it last

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

Trusted Advisor
Posts: 1,137

Re: Filter data based on how long it last

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

Thanks,
Jag
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 481 views
  • 1 like
  • 3 in conversation