DATA Step, Macro, Functions and more

TOTAL NUMBER OF DUPLICATES ROWS

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 125
Accepted Solution

TOTAL NUMBER OF DUPLICATES ROWS

I'd like to create a var "count" for the total number of repated rows in combination of multiple variables as shown in data "want". I know how to accomplish this using proc freq and merging output data back to original file. I wonder if i can do it in data step, as shown how my program had failed to do so below. I'm using SAS 9.4.

Could you please help with direct correction on my code below?

data have;
input id cat dog ant;
datalines;
1 1 2 3
1 1 2 3
1 1 2 3
1 1 2 3
2 2 6 0
3 3 4 5
4 4 6 7
;
run; 

data want;
input id cat dog ant count;
datalines;
1 1 2 3 4
1 1 2 3 4
1 1 2 3 4
1 1 2 3 4
2 2 6 0 1
3 3 4 5 1
4 4 6 7 1
;
run; 

PROC SORT DATA=have;
BY id;
RUN;
DATA HAVE1; SET HAVE;
RETAIN COUNT;
BY ID;
IF FIRST.ID THEN VISIT = 1; ELSE VISIT+1;
IF LAST.ID THEN COUNT=VISIT;
RUN;

 


Accepted Solutions
Solution
‎04-22-2017 11:08 AM
PROC Star
Posts: 7,363

Re: TOTAL NUMBER OF DUPLICATES ROWS

PROC SORT DATA=have;
  BY id;
RUN;

DATA HAVE1;
  do until(last.id);
    SET HAVE;
    BY ID;
    if first.id then count=1;
    else count+1;
  end;
  do until(last.id);
    SET HAVE;
    BY ID;
    output;
  end;
RUN;

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
Solution
‎04-22-2017 11:08 AM
PROC Star
Posts: 7,363

Re: TOTAL NUMBER OF DUPLICATES ROWS

PROC SORT DATA=have;
  BY id;
RUN;

DATA HAVE1;
  do until(last.id);
    SET HAVE;
    BY ID;
    if first.id then count=1;
    else count+1;
  end;
  do until(last.id);
    SET HAVE;
    BY ID;
    output;
  end;
RUN;

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 125

Re: TOTAL NUMBER OF DUPLICATES ROWS

Баярлалаа. Thank you.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 119 views
  • 2 likes
  • 2 in conversation