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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.