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 |
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;
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;
Thanks for the quick reply....
In this case Jack worked two different periods.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.