topic Re: compare colums to create new coloum in SAS Programming

compare colums to create new coloum

sasg — Sat, 18 Jun 2011 04:53:05 GMT

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

Re: compare colums to create new coloum

zilok — Sat, 18 Jun 2011 23:54:46 GMT

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

Re: compare colums to create new coloum

sasg — Sun, 19 Jun 2011 02:13:08 GMT

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

Thanks,
Bhavani.

Re: compare colums to create new coloum

SASJedi — Sun, 19 Jun 2011 03:28:30 GMT

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;

Re: compare colums to create new coloum

sasg — Sun, 19 Jun 2011 06:21:17 GMT

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

Re: compare colums to create new coloum

yonib — Sun, 19 Jun 2011 11:14:30 GMT

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....

Re: compare colums to create new coloum

Ksharp — Mon, 20 Jun 2011 07:05:24 GMT

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

Re: compare colums to create new coloum

sasg — Mon, 20 Jun 2011 18:32:58 GMT

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

Re: compare colums to create new coloum

SASJedi — Tue, 21 Jun 2011 01:50:46 GMT

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.

Re: compare colums to create new coloum

sasg — Mon, 01 Aug 2011 22:55:45 GMT

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,

Re: compare colums to create new coloum

art297 — Tue, 02 Aug 2011 00:30:53 GMT

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;

compare colums to create new coloum

Howles — Tue, 02 Aug 2011 01:46:46 GMT

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