topic Re: Count number of categories across multiple columns in SAS Programming

Count number of categories across multiple columns

Cruise — Fri, 02 Nov 2018 14:23:20 GMT

Hi guys,

My data HAVE has variables to indicate the number of certain categories of facilities found in each ZIP-code area. For instance, num_PCB means number of facilities known to discharge PCBs to an environment for each ZIP codes and same applies to the rest of num_: variables. My objective is to count the number of different categories of facilities per ZIP-code. The desired variable is 'n_diff_cat'.

Data shows that num_V, num_B, num_c exist in ZIP-code 10013 but not num_PCB and num_P. Therefore, 'n_diff_cat' would take value 3.

DATA Z.HAVE; 
INPUT zip num_PCB num_P num_V num_B num_C n_diff_cat;
CARDS;
10013 0 0 7 1 4 3 
10031 0 0 1 0 0 1
10037 0 0 1 0 1 2
10040 0 0 3 0 3 2
10301 1 1 1 0 1 4
10309 1 1 1 0 0 3
10312 0 0 2 1 1 3
10314 1 1 3 0 2 4
10461 1 1 0 0 0 2
10474 0 0 6 0 1 2
10475 0 0 3 1 1 3
10504 0 0 1 0 1 2
10505 0 0 2 0 2 2
10506 0 0 6 1 5 3
10509 0 0 3 0 2 2
10512 0 0 3 0 3 2
10516 0 0 0 0 0 0
10519 1 1 0 0 0 2
10520 0 0 3 0 2 2
10543 0 0 5 0 3 2
;

Thanks,

I appreciate your time!

Re: Count number of categories across multiple columns

gamotte — Fri, 02 Nov 2018 14:33:01 GMT

Hello,

data have; 
    input zip num_PCB num_P num_V num_B num_C;
    array nums(*) num_:;

    n_diff_cat=0;

    do i=1 to dim(nums);
        n_diff_cat=n_diff_cat+(nums(i)>0);
    end;

    drop i;

    cards;
10013 0 0 7 1 4 
10031 0 0 1 0 0
10037 0 0 1 0 1
10040 0 0 3 0 3
10301 1 1 1 0 1
10309 1 1 1 0 0
10312 0 0 2 1 1
10314 1 1 3 0 2
10461 1 1 0 0 0
10474 0 0 6 0 1
10475 0 0 3 1 1
10504 0 0 1 0 1
10505 0 0 2 0 2
10506 0 0 6 1 5
10509 0 0 3 0 2
10512 0 0 3 0 3
10516 0 0 0 0 0
10519 1 1 0 0 0
10520 0 0 3 0 2
10543 0 0 5 0 3
;
run;

Re: Count number of categories across multiple columns

Cruise — Fri, 02 Nov 2018 14:37:16 GMT

THANK YOU VERY MUCH. WORKED OUT.

Re: Count number of categories across multiple columns

gamotte — Fri, 02 Nov 2018 14:43:26 GMT

Less readable but shorter :

data have; 
    input zip num_PCB num_P num_V num_B num_C;
    array nums(*) num_:;

    n_diff_cat=dim(nums)-count(cats(of nums(*)),'0');

    cards;
10013 0 0 7 1 4 
10031 0 0 1 0 0
10037 0 0 1 0 1
10040 0 0 3 0 3
10301 1 1 1 0 1
10309 1 1 1 0 0
10312 0 0 2 1 1
10314 1 1 3 0 2
10461 1 1 0 0 0
10474 0 0 6 0 1
10475 0 0 3 1 1
10504 0 0 1 0 1
10505 0 0 2 0 2
10506 0 0 6 1 5
10509 0 0 3 0 2
10512 0 0 3 0 3
10516 0 0 0 0 0
10519 1 1 0 0 0
10520 0 0 3 0 2
10543 0 0 5 0 3
;

Re: Count number of categories across multiple columns

novinosrin — Fri, 02 Nov 2018 15:16:32 GMT

data have_without_loop; 
    input zip num_PCB num_P num_V num_B num_C;
    array nums(*) num_:;
	n_diff_cat=lengthn(compress(cats(of nums(*)),'0 '));
    cards;
10013 0 0 7 1 4 
10031 0 0 1 0 0
10037 0 0 1 0 1
10040 0 0 3 0 3
10301 1 1 1 0 1
10309 1 1 1 0 0
10312 0 0 2 1 1
10314 1 1 3 0 2
10461 1 1 0 0 0
10474 0 0 6 0 1
10475 0 0 3 1 1
10504 0 0 1 0 1
10505 0 0 2 0 2
10506 0 0 6 1 5
10509 0 0 3 0 2
10512 0 0 3 0 3
10516 0 0 0 0 0
10519 1 1 0 0 0
10520 0 0 3 0 2
10543 0 0 5 0 3
;
run;

Re: Count number of categories across multiple columns

gamotte — Fri, 02 Nov 2018 15:21:33 GMT

I tried such a solution but was stumped by the unit length of empty strings.

Re: Count number of categories across multiple columns

novinosrin — Fri, 02 Nov 2018 15:25:38 GMT

🙂 if may i suggest a small nit for the assignment vs sum in your loop, the below is an equivalent

 n_diff_cat=0;

    do i=1 to dim(nums);
        n_diff_cat+(nums(i)>0);
    end;

Re: Count number of categories across multiple columns

Cruise — Fri, 02 Nov 2018 15:36:50 GMT

i really like this solution.

Re: Count number of categories across multiple columns

gamotte — Fri, 02 Nov 2018 15:41:37 GMT

Yes, but there is an implict retain to be aware of.

I usually use the longer form when i don't take advantage of the retain.

Re: Count number of categories across multiple columns

novinosrin — Fri, 02 Nov 2018 15:51:14 GMT

data have_without_loop(drop=_t); 
    input zip num_PCB num_P num_V num_B num_C;
    _t = put (0, rb8.) ;
    array nums(*) num_:;
    n_diff_cat = n(of nums(*))-count(put(peekclong (addrlong(nums[1]), 40), $40.),_t) ;
    cards;
10013 0 0 7 1 4 
10031 0 0 1 0 0
10037 0 0 1 0 1
10040 0 0 3 0 3
10301 1 1 1 0 1
10309 1 1 1 0 0
10312 0 0 2 1 1
10314 1 1 3 0 2
10461 1 1 0 0 0
10474 0 0 6 0 1
10475 0 0 3 1 1
10504 0 0 1 0 1
10505 0 0 2 0 2
10506 0 0 6 1 5
10509 0 0 3 0 2
10512 0 0 3 0 3
10516 0 0 0 0 0
10519 1 1 0 0 0
10520 0 0 3 0 2
10543 0 0 5 0 3
;
run;

Re: Count number of categories across multiple columns

Cruise — Fri, 02 Nov 2018 15:58:03 GMT

Not sure, if it's super user friendly? 🙂 average SAS users like me might like more intuitive and readable solutions, I guess.

Re: Count number of categories across multiple columns

novinosrin — Fri, 02 Nov 2018 16:05:05 GMT

I agree, just added recipe to the buffet as my first coffee of the day kicked in 🙂

Re: Count number of categories across multiple columns

novinosrin — Fri, 02 Nov 2018 16:07:31 GMT

yes and true, however in your case implicit retain is obsolete due to your initial assignment

 n_diff_cat=0;

for every iteration of the datastep

Re: Count number of categories across multiple columns

FreelanceReinh — Fri, 02 Nov 2018 16:37:50 GMT

Hi @gamotte and @novinosrin,

I wouldn't know why "the number of certain categories of facilities found in" a ZIP-code area cannot be greater than 9. In this case, however, both alternative suggestions involving the CATS function would fail in general:

dim(nums)-count(cats(of nums(*)),'0') subtracts too much for values such as 10, 20, etc.
lengthn(compress(cats(of nums(*)),'0 ')) counts, e.g., 11, 12, etc. as two values.

The "APP function" approach does not seem to have this weakness.

Re: Count number of categories across multiple columns

novinosrin — Fri, 02 Nov 2018 16:39:35 GMT

Nice catch boss @FreelanceReinh My oh my , never expected that level of diligence. Added to my notes!!! and Thanks a lot indeed

Re: Count number of categories across multiple columns

gamotte — Fri, 02 Nov 2018 16:40:30 GMT

You raise a good point 😉

Re: Count number of categories across multiple columns

novinosrin — Fri, 02 Nov 2018 17:15:26 GMT

Sir @FreelanceReinh Logic comes to mind in weird locations as I am away from sas right now I am at the Depaul cafe and so haven't tested this, but since it struck my mind, I just want to get it out there. Will test when I go back to the lab 🙂

n_diff_cat=countw(compress(catx(' ',of nums(*)),'0'));