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 |
/* 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.
/* 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.
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.