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-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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