BookmarkSubscribeRSS Feed
sasg
Calcite | Level 5
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
11 REPLIES 11
zilok
Calcite | Level 5
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
Calcite | Level 5
it can be more than two id's ...lag function won't work....is there any other way?

Thanks,
Bhavani.
SASJedi
SAS Super FREQ
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;
Check out my Jedi SAS Tricks for SAS Users
sasg
Calcite | Level 5
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
Super User
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
Calcite | Level 5
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
SAS Super FREQ
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.
Check out my Jedi SAS Tricks for SAS Users
yonib
SAS Employee
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
Calcite | Level 5

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
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1365 views
  • 0 likes
  • 7 in conversation