topic Create a variable count that increments for each duplicate variable in a dataset in SAS Programming

Create a variable count that increments for each duplicate variable in a dataset

MattSan — Fri, 13 Jan 2017 09:33:35 GMT

Hello all,

Please can you assist with the following problem? I need to count the number of repeated characters in a dataset for each repeated "char" data.

Original data:

Num	Char
100060002	A
100060004	A
100060008	A
100060010	B
100060013	B
100060014	B
100060015	C
100060020	C
100060021	D

Required output:

Num	Char	Count
100060002	A	1
100060004	A	2
100060008	A	3
100060010	B	1
100060013	B	2
100060014	B	3
100060015	C	1
100060020	C	2
100060021	D	1

Any assitance would be greatly appreciated.

My code thus far:

DATA TEST;
	SET TEST;
	BY CHAR;
	PUT COUNT 1.;
	
	IF FIRST.CHAR THEN;
	COUNT=0;
	COUNT+1;
RUN;

Re: Create a variable count that increments for each duplicate variable in a dataset

Kurt_Bremser — Fri, 13 Jan 2017 09:50:51 GMT

First of all, this is the way to post example data:

data have;
input num char $;
cards;
100060002 A
100060004 A
100060008 A
100060010 B
100060013 B
100060014 B
100060015 C
100060020 C
100060021 D
;
run;

It allows us to recreate your data with a simple copy/paste and run.

Next, your use of the if - then is erroneous. The semicolon immediately after the then causes the conditional statement to end.

This (applied to test data from above) will do it:

data want;
set have;
by char;
if first.char
then count = 1;
else count + 1;
run;

Re: Create a variable count that increments for each duplicate variable in a dataset

DanielSantos — Fri, 13 Jan 2017 09:52:39 GMT

Hi, I think you've got it right, just loose the ; after the then


DATA TEST;
	SET TEST;
	BY CHAR;
	PUT COUNT 1.;
	
	IF FIRST.CHAR THEN COUNT=0;
	COUNT+1;
RUN;

Daniel Santos @ www.cgd.pt

Re: Create a variable count that increments for each duplicate variable in a dataset

Kurt_Bremser — Fri, 13 Jan 2017 10:00:52 GMT

Addendum: if the char variable does not come in a strictly ascending sequence, use

by char notsorted;