topic Grouping dates in SAS Programming

Grouping dates

sg_kr — Thu, 25 Oct 2018 17:28:12 GMT

Hi ,

i have data like below.

Agt_No	pol	EMP_ID	Reg_date
1	12	a	1/1/2018
1	13	a	1/7/2018
1	14	b	1/3/2018
1	15	b	1/4/2018
2	16	c	1/1/2018
2	17	c	1/1/2018
2	18	d	1/1/2018
2	19	a	1/1/2018
3	20	b	1/9/2017
3	21	c	1/1/2018
3	22	a	1/5/2016
4	23	b	1/1/2018
4	24	c	1/1/2018
4	25	a	1/1/2017

i want to get count of first EMP_id per agent by the earlier register date.

desired output like:

Agt_No	pol	EMP_ID	Reg_date
1	12	a	1/1/2018
1	14	b	1/3/2018
2	16	c	1/1/2018
2	18	d	1/1/2018
3	20	b	1/9/2017
3	22	a	1/5/2016
4	23	b	1/1/2018
4	25	c	1/1/2017

code i wrote is shown below:

data have ;
input agt_no 2. pol_no 2. EMP_id $2. reg_date mmddyy10.;
/*informat reg_date date9.;*/
format reg_date mmddyy10.;
datalines;
1 12 a 1/1/2018
1 13 a 1/7/2018
1 14 b 1/3/2018
1 15 b 1/4/2018
2 16 c 1/1/2018
2 17 c 1/1/2018
2 18 d 1/1/2018
2 19 a 1/1/2018
3 20 b 1/9/2017
3 21 c 1/1/2018
3 22 a 1/5/2016
4 23 b 1/1/2018
4 24 c 1/1/2018
4 25 a 1/1/2017
;
run;

proc sort data=have;
by agt_no emp_id ;
run;

/*grouping the data by EMP_ID*/
data want;
set have;
by agt_no emp_id;
if first.emp_id then n=1;
count+n;
if n=1;
drop emp_id count n;
/*format regdate date9.;*/
pol_no=EMP_ID;
run;

the main issue is with the reg_date, i am able to get the agt_no per emp_id but not with the ealier reg_date.

kindly please help on this

Re: Grouping dates

Allaluiah — Thu, 25 Oct 2018 17:43:22 GMT

Hello @sg_kr It's apparent having followed many of your posts you don't ever seem to mark the answers as accepted/solved. While arguably this is the best forum on earth and people share joy in writing sas programs than any of the acknowledgements they get, it is our responsibility to extend courtesy and goodwill.

I do appreciate all the questions however please offer the minimum courtesy. It's great to be human after all. Well well!!!!!!!!!

Re: Grouping dates

mkeintz — Thu, 25 Oct 2018 18:41:15 GMT

Force your proc sort to start each agt_no/emp_id with the earliest reg_date.

Instead of:

proc sort data=have;
  by agt_no emp_id ;
run;

use:

proc sort data=have;
by agt_no emp_id  reg_date;
run;

Also instead of

if first.emp_id then n=1;
count+n;
if n=1;

just use:

if first.emp_id;

You are not using the N variable, so don't bother creating it.

Re: Grouping dates

sg_kr — Fri, 26 Oct 2018 11:01:31 GMT

thanks for your response.

if i sort by reg_date, agt_no & emp_id in sorting step.

and later if i use emp_id as by variable.

then we are will get an error as belo

ERROR: BY variables are not properly sorted on data set WORK.HAVE.