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

Hi ,

i have data like below.

Agt_NopolEMP_ID Reg_date
112a1/1/2018
113a1/7/2018
114b1/3/2018
115b1/4/2018
216c1/1/2018
217c1/1/2018
218d1/1/2018
219a1/1/2018
320b1/9/2017
321c1/1/2018
322a1/5/2016
423b1/1/2018
424c1/1/2018
425a1/1/2017

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

 

desired output like:

Agt_NopolEMP_ID Reg_date
112a1/1/2018
114b1/3/2018
216c1/1/2018
218d1/1/2018
320b1/9/2017
322a1/5/2016
423b1/1/2018
425c1/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

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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.

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

3 REPLIES 3
Allaluiah
Quartz | Level 8

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

mkeintz
PROC Star

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.

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
sg_kr
Obsidian | Level 7

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.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 3 replies
  • 1008 views
  • 5 likes
  • 3 in conversation