BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
greesamu
Obsidian | Level 7

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

/* 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. 

--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26

/* 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. 

--
Paige Miller
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 697 views
  • 1 like
  • 2 in conversation