Help with Code to get the desired output

[Samj]

Hello Everyone, 

I am new to SAS and am having hard time coming up with the sas code for the following situation. I have information on Firm name, Year, Directors, and Date of joining. The data I have looks as follows:

Firm           Year          Director     Date of Joining

A               1995          SAM          1982

A               1995          TOM          1996

A               1995          TINA         

A               1995          ROB         

A               1995          JACK         

A               1996          SAM          1982

A               1996          TOM          1996

A               1996          TINA         

A               1996          ROB         

A               1996          JACK         

A               1997          SAM          1982

A               1997          TOM          1996

A               1997          TINA         

A               1997          ROB         

A               1997          JACK     

A               1998          SAM         

A               1998          TOM          1996

A               1998          TINA         

A               1998          ROB         

A               1998          JACK         

A               1999          SAM         

A               1999          TOM          1996

A               1999          TINA         

A               1999          ROB         

A               1999          JACK         

A               2000          SAM         

A               2000          TOM          1996

A               2000          TINA         

A               2000          ROB          2001

A               2000          JACK         

A               2001          TINA         

A               2001          ROB          2001

A               2001          JACK         

If there are two joining dates for a given firm- year and both are lower than the YEAR, I want to pick the earliest date. If YEAR = one of the joining date, then I want APPT_Date to that Date of joining. In short, I am trying to get an Output as follows:

Firm           Year          Director     Date of Joining     Appt_date

A               1995          SAM          1982                     1982

A               1995          TOM          1996                     1982

A               1995          TINA                                      1982

A               1995          ROB                                      1982  

A               1995          JACK                                     1982

A               1996          SAM          1982                     1996

A               1996          TOM          1996                     1996

A               1996          TINA                                      1996

A               1996          ROB                                      1996

A               1996          JACK                                      1996

A               1997          SAM          1982                     1996

A               1997          TOM          1996                     1996

A               1997          TINA                                      1996

A               1997          ROB                                      1996

A               1997          JACK                                     1996

A               1998          SAM                                      1996

A               1998          TOM          1996                     1996

A               1998          TINA                                      1996

A               1998          ROB                                      1996

A               1998          JACK                                      1996

A               1999          SAM                                      1996

A               1999          TOM          1996                     1996

A               1999          TINA                                      1996

A               1999          ROB                                      1996

A               1999          JACK                                      1996

A               2000          SAM                                       1996

A               2000          TOM          1996                      1996

A               2000          TINA                                      1996

A               2000          ROB          2001                      1996

A               2000          JACK                                      1996

A               2001          TINA                                        2001

A               2001          ROB          2001                       2001

A               2001          JACK                                       2001

A               2002          TINA                                        2001

A               2002          ROB          2001                       2001

A               2002          JACK                                       2001    

My actual sample has over 500 firms and ranges from 1996 to 2010. I would appreciate if someone would guide me in getting the output I deisire.

Thank you in advance.

[PGStats]

firm=A and Year=1997 with joining year=1982 and 1996 shouldn't APPT_date be 1982? - PG

[Samj]

Dear PG Stats,

Once the YEAR = joining date, I want the Appt_DATE to be that joining date till there is another joining date = YEAR. For instance, Appt_date = 1996 from year=1996 onwards till there is another joining date = Year.

[Haikuo · Accepted solution]

Hi ,

Looks like you would need a 2XDOW or interleaving stacking. Below is the DOW approach. BTW, please update your first post to include the additional rules addressed in your answer to PG, so that others who are interest in your question can get the whole story without digging into following every single posts.

data have;

infile cards truncover;

input Firm$ Year Director$   DateJoining;

cards;

A               1995          SAM          1982

A               1995          TOM          1996

A               1995          TINA      

A               1995          ROB      

A               1995          JACK      

A               1996          SAM          1982

A               1996          TOM          1996

A               1996          TINA      

A               1996          ROB      

A               1996          JACK      

A               1997          SAM          1982

A               1997          TOM          1996

A               1997          TINA      

A               1997          ROB      

A               1997          JACK  

A               1998          SAM      

A               1998          TOM          1996

A               1998          TINA      

A               1998          ROB      

A               1998          JACK      

A               1999          SAM      

A               1999          TOM          1996

A               1999          TINA      

A               1999          ROB      

A               1999          JACK      

A               2000          SAM      

A               2000          TOM          1996

A               2000          TINA      

A               2000          ROB          2001

A               2000          JACK      

A               2001          TINA      

A               2001          ROB          2001

A               2001          JACK      

;

data want;

  do until (last.year);

    set have;

      by firm year;

           _date1=min(_date1, datejoining);

        _date2=ifn(datejoining=year,year,_date2);

        _flag=ifn(datejoining=year,1,_flag);

        if last.year then appt_date=coalesce(_date2,appt_date*_flag,_date1);

end;

  do until (last.year);

    set have;

      by firm year;

       output;

  end;

  retain  _flag appt_date;

  drop _:;

run;

proc print;run;

Haikuo

[Samj]

Thank you. You are a life saver!