compare colums to create new coloum

[sasg]

company id date
xyz 123 02/11/2009
xyz 123 03/11/2009
abc 124 05/11/2009

I have to compute new coloum called date1.In calculating date1,
i have to check below conditions.

1)compare two consecutive id's(ex: 123 and 123)...if these two id's match
second id date should be the new computed coloum date.

If two id's doen't match new computed coloum would be blank.

Final dataset:

company id date date1
xyz 123 02/11/2009 03/11/2009
xyz 123 03/11/2009
abc 124 05/11/2009

How can i do this one?

Thanks,
sasg

[zilok]

If this is your objective, a simple solution is to reverse sort the data and do the following:

format date1 ddmmyyyy10.;
if id = lag(id) then date1 = lag(date);

and sort back the data to its original form.
Use _N_ to reverse sort the data.

Thanks,
zilok

[sasg]

it can be more than two id's ...lag function won't work....is there any other way?

Thanks,
Bhavani.

[SASJedi]

Why wouldn't the lag function work? For example:

data start;
input Text:$3. ID:3. Date:mmddyy10.;
format date mmddyy10.;
datalines;
xyz 123 02/11/2009
xyz 123 03/11/2009
xyz 123 04/11/2009
xyz 123 05/11/2009
abc 124 05/11/2009
def 125 02/11/2009
def 125 03/11/2009
;
run;
proc sort data=start ;
by id descending date;
run;
data final;
set start;
by id;
date2=lag1(date);
if (first.ID and last.ID) or first.id then call missing(date2);
format date2 mmddyy10.;
run;
proc sort data=final;
by id date;
run;

[sasg]

thanks for the response

but output for sample data should look like this

xyz 123 02/11/2009 03/11/2009
xyz 123 03/11/2009 04/11/2009
xyz 123 04/11/2009 05/11/2009
xyz 123 05/11/2009
abc 124 05/11/2009
def 125 02/11/2009 03/11/2009
def 125 03/11/2009 .

but if i execute the code first date is comming as missing..and also remaining coloums it is calculating wrong....can u pls suggest me what changes i have to make?

Thanks,
sasg

[Ksharp]

Then use Merge Skill.
[pre]




data start;
input Text:$3. ID:3. Date:mmddyy10.;
format date mmddyy10.;
datalines;
xyz 123 02/11/2009
xyz 123 03/11/2009
xyz 123 04/11/2009
xyz 123 05/11/2009
abc 124 05/11/2009
def 125 02/11/2009
def 125 03/11/2009
;
run;
data want;
merge start start(keep=id date rename=(id=_id date=_date) firstobs=2);
date1=ifn(id=_id,_date,.);
format date1 mmddyy10.;
drop _:;
run;
[/pre]

Ksharp

[sasg]

If i use this logic everything looks fine....but for first record it is comming as .,but it should be second records date.

Thanks,
sasg

[SASJedi]

I copied the code from my previous post, pasted it into a SAS9.2 editor window running on Windows 7 64bit, then submitted the code. The final output looks like this:

xyz 123 02/11/2009 03/11/2009
xyz 123 03/11/2009 04/11/2009
xyz 123 04/11/2009 05/11/2009
xyz 123 05/11/2009 .
abc 124 05/11/2009 .
def 125 02/11/2009 03/11/2009
def 125 03/11/2009 .

To me, this appears to be the output you want. I'm having difficulty understanding why your results are not identical. Did you execute the final sort step? If not, the obs are in reverse order, and the "first"record will be missing date2.

Please run a PROC PRINT on your final data set and paste the results here so we can compare and troubleshoot further.

[yonib]

You can try the look ahead ability,
For example:

data aaa;
format date ddmmyy10.;
input company $ id date ddmmyy10.;
cards;
xyz 123 02/11/2009
xyz 123 03/11/2009
xyz 123 04/11/2009
xyz 123 05/11/2009
abc 124 05/11/2009
;
run;
proc sort data=aaa;
by company date;
quit;

data bbb;
set aaa end=eof;
by company;
if not eof then
set aaa(firstobs=2 keep=date rename=date=date_after);
if first.company and last.company then date_after=date;
if eof then date_after=.;
run;

Although using the lag function techniques as the other here suggest should work fine....

[sasg]

Hi,

      I have issue with this....different company  is having same id ....in that scenario i'm getting error.

      how can i handle that one.

      my code:

data bbb; 

set aaa end=eof; 

by company; 

if not eof then

   set aaa(firstobs=2 keep=date rename=date=date_after);

if first.company and last.company then date_after=date;

if last.company then date_after=.;

run;

example scenario:

company    id   date

xyz       123   02/11/2009

xyz       123   03/11/2009

abc       123   05/11/2009

output should be:

company    id   date           next_date

xyz       123   02/11/2009    03/11/2009

xyz       123   03/11/2009     .

abc       123   05/11/2009     .

current output:

company    id   date           next_date

xyz       123   02/11/2009    03/11/2009

xyz       123   03/11/2009    05/11/2009

abc       123   05/11/2009     .

Thanks,

rk

[art297]

It looks like you've already been offered a number of ways to do this but, since you asked, how about:

data aaa;

  input company $ id   date mmddyy8.;

  format date date9.;

  cards;

xyz       123  02/11/2009

xyz       123  03/11/2009

abc       123  05/11/2009

bbb       124  01/12/2010

bbb       124  01/13/2010

bbb       124  01/14/2010

bbb       124  01/15/2010

bbb       124  01/16/2010

bbb       124  01/13/2010

bbb       124  01/14/2010

;

proc sort data=aaa;

  by company id descending date;

run;

data bbb (drop=date_after);

  set aaa;

  by company id;

  retain date_after;

  format next_date date9.;

  if first.id then call missing(next_date);

  else next_date=date_after;;

  date_after=date;

run;

proc sort data=bbb;

  by company id date;

run;

[Howles]

See http://www.sascommunity.org/wiki/Look-Ahead_and_Look-Back