BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Pandu
Fluorite | Level 6

Hi ,

I have a date set with variables ID, lead id , ctime , utime, name.

 

Here i want to extract the person name utime start date and end date.

 

1) ID is same but leads will change, according to that i need to extract the name (i want to know the person name who worked for which period )

 

Data set :

 

ID

lead id

ctime

utime

Name

2254

35217

6/06/2009

6/06/2009

Jack

2254

35217

6/06/2009

29/06/2009

Jack

2254

35217

6/06/2009

30/08/2010

Jack

2254

35217

6/06/2009

3/09/2010

Jack

2254

35217

6/06/2009

6/09/2010

Jack

2254

35217

6/06/2009

26/11/2010

Jack

2254

35217

6/06/2009

29/11/2010

Jack

2254

35267

6/06/2009

1/12/2010

Prem

2254

35267

6/06/2009

29/12/2010

Prem

2254

35267

6/06/2009

8/02/2011

Prem

2254

35267

6/06/2009

14/03/2011

Prem

2254

35217

6/06/2009

16/03/2011

Jack

2254

35217

6/06/2009

20/04/2011

Jack

2254

35217

6/06/2009

10/06/2011

Jack

2254

35217

6/06/2009

30/06/2011

Jack

2254

35217

6/06/2009

6/07/2011

Jack

2254

35217

6/06/2009

14/07/2011

Jack

 

In the above data set . Jack worked from 6/06/2009 to 29/11/2010 under ID 2254 and  under lead id 35217.

after that Prem worked from 1/12/2010 to 14/03/2011 under ID 2254 and  under lead id 35267.

 

here again Jack worked from 16/03/2011 to 16/03/2011 under ID 2254 and  under lead id 35217.

 

output should be :

ID

lead id

ctime

utime

Name

2254

35217

6/06/2009

6/06/2009

Jack

 
 

2254

35217

6/06/2009

29/11/2010

Jack

2254

35267

6/06/2009

1/12/2010

Prem

  
  

2254

35267

6/06/2009

14/03/2011

Prem

2254

35217

6/06/2009

16/03/2011

Jack

 
 

2254

35217

6/06/2009

14/07/2011

Jack

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

Something like this?

 

data have;
input ID $ lead_id $ (ctime utime)(:ddmmyy10.) Name $;
format ctime utime ddmmyy10.;
datalines;
2254 35217 6/06/2009 6/06/2009 Jack
2254 35217 6/06/2009 29/06/2009 Jack
2254 35217 6/06/2009 30/08/2010 Jack 
2254 35217 6/06/2009 3/09/2010 Jack
2254 35217 6/06/2009 6/09/2010 Jack
2254 35217 6/06/2009 26/11/2010 Jack
2254 35217 6/06/2009 29/11/2010 Jack
2254 35267 6/06/2009 1/12/2010 Prem
2254 35267 6/06/2009 29/12/2010 Prem
2254 35267 6/06/2009 8/02/2011 Prem
2254 35267 6/06/2009 14/03/2011 Prem
2254 35217 6/06/2009 16/03/2011 Jack
2254 35217 6/06/2009 20/04/2011 Jack
2254 35217 6/06/2009 10/06/2011 Jack
2254 35217 6/06/2009 30/06/2011 Jack
2254 35217 6/06/2009 6/07/2011 Jack
2254 35217 6/06/2009 14/07/2011 Jack
;

data want;
   set have;
   by lead_id notsorted;
   if first.lead_id or last.lead_id;
run;

View solution in original post

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

Something like this?

 

data have;
input ID $ lead_id $ (ctime utime)(:ddmmyy10.) Name $;
format ctime utime ddmmyy10.;
datalines;
2254 35217 6/06/2009 6/06/2009 Jack
2254 35217 6/06/2009 29/06/2009 Jack
2254 35217 6/06/2009 30/08/2010 Jack 
2254 35217 6/06/2009 3/09/2010 Jack
2254 35217 6/06/2009 6/09/2010 Jack
2254 35217 6/06/2009 26/11/2010 Jack
2254 35217 6/06/2009 29/11/2010 Jack
2254 35267 6/06/2009 1/12/2010 Prem
2254 35267 6/06/2009 29/12/2010 Prem
2254 35267 6/06/2009 8/02/2011 Prem
2254 35267 6/06/2009 14/03/2011 Prem
2254 35217 6/06/2009 16/03/2011 Jack
2254 35217 6/06/2009 20/04/2011 Jack
2254 35217 6/06/2009 10/06/2011 Jack
2254 35217 6/06/2009 30/06/2011 Jack
2254 35217 6/06/2009 6/07/2011 Jack
2254 35217 6/06/2009 14/07/2011 Jack
;

data want;
   set have;
   by lead_id notsorted;
   if first.lead_id or last.lead_id;
run;
Pandu
Fluorite | Level 6

Thanks for the quick reply....

 

In this case Jack worked two different periods.

 

 

Pandu
Fluorite | Level 6

Thank you very much....yes, this code suites my requirement. i will test my data and if i have any problem will come back.

 

Thanks again.

PeterClemmensen
Tourmaline | Level 20

Anytime. Feel free to reach out if you encounter problems. 

 

 

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
  • 4 replies
  • 760 views
  • 1 like
  • 2 in conversation