topic How to count distinct values across columns in SAS Programming

How to count distinct values across columns

greesamu — Thu, 18 Jan 2024 16:43:36 GMT

I have a wide dataset, and each subject_id has variables for 2015-2020.

For the zip code variable, for example, I want to know how many different values are recorded for each subject across the 5 columns of zip code data (ideally not counting missing data). I'm sure SAS SQL could do this , but I'm struggling to figure what the operation would be called.

Below is an example of what an excerpt of my data looks like, and the zip_count variable is what I'm hoping to calculate.

subject_id	zip_15	zip_16	zip_17	zip_18	zip_19	zip_20	zip_count
a	55555	55555	55555	55555	55555	55555	1
b	.	.	.	55555	55555	55555	1
c	55555	55555	55555	44444	44444	44444	2
d	.	.	55555	55555	44444	44444	2

Re: How to count distinct values across columns

PaigeMiller — Thu, 18 Jan 2024 16:54:58 GMT

/* UNTESTED CODE */

data want;
    set have;
    array z zip_15-zip_20;
    call sortn(of z(*));
    zip_count=0;
    do i=2 to dim(z);
        if not missing(z(i-1)) then zip_count=1;
        if z(i)^z(i-1) then zip_count=zip_count+1;
    end;
run;

If you want tested code, please provide data as working SAS data step code (examples and instructions), and not as a screen capture and not as Excel.

Re: How to count distinct values across columns

PaigeMiller — Thu, 18 Jan 2024 17:14:29 GMT

Correction

data want;
    set have;
    array z zip_15-zip_20;
    call sortn(of z(*));
    zip_count=1;
    do i=2 to dim(z);
        if z(i)^=z(i-1) and missing(z(i-1)) then zip_count=1;
        else if z(i)^=z(i-1) and not missing(z(i-1)) then zip_count=zip_count+1;
    end;
run;