Need a code

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Need a code


Hi All,

I need your help with a dataset. I have the following information with me:

Firm     Date          CL

A          1/1/2006    

A          1/10/2006    

A          2/5/2006    

A          2/8/2006      YES   

A          10/21/2008    

A          10/25/2008    

A          10/30/2008    

A          11/3/2008    

A          11/11/2008      YES

B          1/11/2005    

B          1/19/2005    

B          2/1/2005    

B          2/10/2005      YES   

B          7/21/2006    

B          7/30/2006    

B          8/15/2006      YES

CL is a character variable. I want to create four new variable: Starting Date, Closing Date, ROUND, and DURATION. Starting Date is when the CL is first recorded. CLosing Date is the date when CL is "YES". ROUND is number of observations between starting date and closing date. DURATION is number of days between starting date and closing date. In short, I am trying to get an output which would look as follows:

FIRM     starting Date     Closing Date     ROUND     DURATION

A          1/1/2006          2/8/2006          4                    39days

A          10/21/2008         11/11/2008     5                    21days

B          1/11/2005          1/10/2005         4                    29days

B          7/21/2006          8/15/2006          3                    25days

Can please someone provide me with a code to get this output?

Thank you in advance.

Priya


Accepted Solutions
Solution
‎02-10-2013 09:02 PM
Super User
Super User
Posts: 6,502

Re: Need a code

data have;

  infile cards truncover firstobs=2 truncover;

  length firm $8 date 8 cl $3 ;

  informat date mmddyy10. ;

  format date yymmdd10. ;

  input firm $ date cl $ ;

cards;

Firm     Date          CL

A          1/1/2006  

A          1/10/2006  

A          2/5/2006  

A          2/8/2006      YES 

A          10/21/2008  

A          10/25/2008  

A          10/30/2008  

A          11/3/2008  

A          11/11/2008      YES

B          1/11/2005  

B          1/19/2005  

B          2/1/2005  

B          2/10/2005      YES 

B          7/21/2006  

B          7/30/2006  

B          8/15/2006      YES

run;

data want ;

  if 0 then set have ;

  do round=1 by 1 until (last.firm or cl='YES');

    set have;

    by firm ;

    if round=1 then starting_date=date;

  end;

  if cl='YES' then do;

    closing_date=date;

    days=closing_date-starting_date+1;

  end;

  format starting_date closing_date yymmdd10. ;

run;

                                             starting_      closing_

Obs    firm       date       cl     round       date          date       days

1      A      2006-02-08    YES      4      2006-01-01    2006-02-08     39

2      A      2008-11-11    YES      5      2008-10-21    2008-11-11     22

3      B      2005-02-10    YES      4      2005-01-11    2005-02-10     31

4      B      2006-08-15    YES      3      2006-07-21    2006-08-15     26

View solution in original post


All Replies
Solution
‎02-10-2013 09:02 PM
Super User
Super User
Posts: 6,502

Re: Need a code

data have;

  infile cards truncover firstobs=2 truncover;

  length firm $8 date 8 cl $3 ;

  informat date mmddyy10. ;

  format date yymmdd10. ;

  input firm $ date cl $ ;

cards;

Firm     Date          CL

A          1/1/2006  

A          1/10/2006  

A          2/5/2006  

A          2/8/2006      YES 

A          10/21/2008  

A          10/25/2008  

A          10/30/2008  

A          11/3/2008  

A          11/11/2008      YES

B          1/11/2005  

B          1/19/2005  

B          2/1/2005  

B          2/10/2005      YES 

B          7/21/2006  

B          7/30/2006  

B          8/15/2006      YES

run;

data want ;

  if 0 then set have ;

  do round=1 by 1 until (last.firm or cl='YES');

    set have;

    by firm ;

    if round=1 then starting_date=date;

  end;

  if cl='YES' then do;

    closing_date=date;

    days=closing_date-starting_date+1;

  end;

  format starting_date closing_date yymmdd10. ;

run;

                                             starting_      closing_

Obs    firm       date       cl     round       date          date       days

1      A      2006-02-08    YES      4      2006-01-01    2006-02-08     39

2      A      2008-11-11    YES      5      2008-10-21    2008-11-11     22

3      B      2005-02-10    YES      4      2005-01-11    2005-02-10     31

4      B      2006-08-15    YES      3      2006-07-21    2006-08-15     26

Contributor
Posts: 28

Re: Need a code

Thanks Tom for the code.

Occasional Contributor
Posts: 12

Re: Need a code

data test;

     infile cards missover;

     informat date mmddyy10.;

     input firm $ date cl $;

cards;

A         1/1/2006

A          1/10/2006

A          2/5/2006

A          2/8/2006      YES

A          10/21/2008

A          10/25/2008

A          10/30/2008

A          11/3/2008

A          11/11/2008      YES

B          1/11/2005

B          1/19/2005

B          2/1/2005

B          2/10/2005      YES

B          7/21/2006

B          7/30/2006

B          8/15/2006      YES

;

proc sql;

  create table test1 as

     select firm,min(date) as starting_date format mmddyy10.,

               max(date) as closing_date format mmddyy10.,

               count(*) as round,max(date)-min(date)+1 as duration

        from test

        group by firm,year(date)

        having cl='YES'

        order by firm;

quit;

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 239 views
  • 5 likes
  • 3 in conversation