topic Re: Duplicate Character IDs = 1 in SAS Programming

Duplicate Character IDs = 1

a_zacMD — Tue, 23 Jun 2020 14:43:29 GMT

I have a dataset with character IDs (mix of numbers and letters) and I want to be able to know the count of the unique IDs when running crosstabs. I am using SAS version 9.4 Thank you in advance.

Here is an example of what I have:

DATASET
OBS	ID	STATUS
1	A123	2
2	A123	2
3	A123	2
4	A123	2
5	3F55	1
6	3F55	1
7	D445	2
8	D445	2
9	D445	2
10	D445	2
11	D445	2

Current crosstab:

CURRENT TABLE
	STATUS
ID	1	2	TOTAL
A123	0	4	4
3F55	2	0	2
D445	0	5	5
	2	9	11

Wanted crosstab:

WANT TO BE ABLE TO COUNT EVERY ID AS 1 (NOTE EVERY ID WILL FALL CONSISTENTLY INTO THE SAME STATUS)

WANTED TABLE
	STATUS
ID	1	2	TOTAL
A123	0	1	1
3F55	1	0	1
D445	0	1	1
	1	2	3

Re: Duplicate Character IDs = 1

Kurt_Bremser — Tue, 23 Jun 2020 14:47:05 GMT

Do a SELECT DISTINCT or SORT with NODUPKEY first.

Re: Duplicate Character IDs = 1

a_zacMD — Wed, 24 Jun 2020 14:42:25 GMT

Thank you so much, it worked!

While checking into it here is what I found, in case anyone out there needs it:

There are 2 types of ways to remove duplicates. You can either remove duplicates where all variable values (nodup - EX1) are compared or remove duplicates for a single variable (nodupkey - EX2), for example by ID:

PROC SORT DATA=DATASET NODUP OUT=EX1;
BY ID;
RUN;
PROC FREQ DATA=EX1;
TABLES ID;
RUN;

PROC SORT DATA=DATASET NODUPKEY OUT=EX2;
BY ID;
RUN;
PROC FREQ DATA=EX2;
TABLES ID;
RUN;