topic Re: Help with Code to get the desired output in SAS Programming

Help with Code to get the desired output

Samj — Sat, 06 Oct 2012 19:46:07 GMT

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.

Re: Help with Code to get the desired output

PGStats — Sat, 06 Oct 2012 22:13:04 GMT

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

Re: Help with Code to get the desired output

Samj — Sat, 06 Oct 2012 22:21:44 GMT

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.

Re: Help with Code to get the desired output

Haikuo — Sun, 07 Oct 2012 01:04:36 GMT

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

Re: Help with Code to get the desired output

Samj — Wed, 10 Oct 2012 03:50:39 GMT

Thank you. You are a life saver!