SAS: to count how many respondents, who have *brand i* in their choice set, have also *brand j* in the choice set

Question

i would be very thankful if you could give me some hints how to do or what and where I may have a look to solve the following task:

For instance, I have a sample of persons (PID), the choice set they faced that consists of a chosen car-brand and other considered car-alternatives (either two or one as some respondents have named two cars they have considered additional to the bought car, some of them - only one).

PID decision alternative brand

1   1   1   BMW
1   0   2   AUDI
1   0   3   Mercedes

2   1   1   AUDI
2   0   2   Mercedes
2   0   3   Land_Rover

3   1   1   Mercedes
3   0   2   BMW
3   0   3   VW

4   1   1   VW
4   0   2   AUDI

5   1   1   BMW

6   1   1   AUDI
6   0   2   VW
6   0   3   VW

7   1   1   Mercedes
7   0   2   AUDI

I would like to count how many respondents, who have brand i in their choice set, have also brand j in the choice set. As an illustration for the data above, I would like to get the following table:

           AUDI  BMW Land_Rover Mercedes VW
AUDI        0     1      1          3*     2
BMW               0      0          2     1
Land Rover               0          1     0
Mercedes                            0     1
VW                                        1**

One reads it as follows: * there are 3 Respondents with Mercedes in the choice set, who also have Audi in their choice set. ** there are 1 respondent with VW in the choice set, who named VW once again as a considered alternative (some kind of loyalty to the brand).

I would appreciate if you could tell me with what procedure I may do it. I have in total 46 brands.

Many thanks in advance.

Vlada

P.S. if a similar question was answered once, I would appreciate a link to the answer and appologize in advance for my not ability to find it.

My solution:

NOTE: It counts a bit different. It count how many persons who have bought a particular car, have named other brands as an alternative. In such a way I am gonna look what brands compete with one another for being in consideration set.

*Counting cars´ combinations in the choice set;

*Clean-Up: Delete unneccessary datasets in the work library;
proc datasets lib=work kill nolist memtype=data;
quit;

*Clear the output window;
ods html close; /* close previous */
ods html; /* open new */

*Counting cars´ combinations in the choice set;

    data have;
    input PID decision alternative brand $;
    datalines;
    1   1   1   BMW
    1   0   2   AUDI
    1   0   3   Mercedes
    2   1   1   AUDI
    2   0   2   Mercedes
    2   0   3   Land_Rover
    3   1   1   Mercedes
    3   0   2   BMW
    3   0   3   VW
    4   1   1   VW
    4   0   2   AUDI
    5   1   1   BMW
    6   1   1   AUDI
    6   0   2   VW
    6   0   3   VW
    7   1   1   Mercedes
    7   0   2   AUDI
    ;;;;
    run;

    data code_brand; 
    input brand $ code_brand;
    datalines;
    AUDI        1
    BMW         2
    Land_Rover  3
    Mercedes    4
    VW          5
    ;;
    run;

    data have_wide; set have;
    by pid;
    keep pid brand1 brand2 brand3;
    retain brand1 brand2 brand3;
    array abrand( 3) $ 20 brand1 brand2 brand3;
    if first.pid then do;
     do i=1 to 3;
      abrand(i)=" ";
     end;
    end;
    abrand(alternative)=brand;
    if last.pid then output;
    run;

    proc freq data=have_wide noprint;
    table brand1*brand2 /out=brand1_2;
    run;

    proc freq data=have_wide noprint;
    table brand1*brand3 /out=brand1_3;
    run;

    proc sql;
    create table temp1 as 
    select t1.brand1, t1.brand2, t1.count as count_1_2, t2.brand3, t2.count as count_1_3, 
    (t1.count+t2.count) as total
    from brand1_2 t1 left join brand1_3 t2
    on t1.brand1=t2.brand1 and t1.brand2=t2.brand3;

    create table cs_count as 
    select t1.brand1 as first_car, t1.brand2 as alternative_car, 
    (case when t1.total is missing then t1.count_1_2
     else t1.total end) as  cs_count,
    t2.code_brand as code_brand2
    from temp1 t1 left join code_brand t2
    on t1.brand2=t2.brand
    order by brand1, brand2;

    /* Reshaping a Dataset from long to wide format with multiple variables*/

    proc transpose data=cs_count out=cs_count_wide prefix=b;
        by first_car;
        id code_brand2;
        var cs_count;
    run; 


    proc sql;
    create table final as 
    select t2.code_brand as counter, 
    (case when t1.first_car is missing then t2.brand
     else t1.first_car end) as first_car, 
    cats('b',t2.code_brand) as code_brand1, 
    t1.b1, t1.b2, . as b3, t1.b4, t1.b5
    from cs_count_wide (keep= first_car b:) t1 full join code_brand t2
    on t1.first_car=t2.brand
    order by t2.code_brand;

    data final;
    set final;
    drop counter first_car;
    run;

    proc iml;
    use final;
    read all var{code_brand1} into name; *create separate vector of brand names;
    read all var _num_ into data; *create separate matrix of data observations;

    n = nrow(data);
    p = ncol(data);
    lower = j(n, p, 0); 
    do i = 2 to n;
    cols = 1:i-1;
    lower[i, cols] = data[i, cols];
    end; *extracts lower diagonal matrix with 0 values at the diagonal;

    print lower;

    upper = j(n, p, 0);
    do i = 1 to n;
       cols=i:p;
       upper[i, cols] = data[i, cols];
    end; *extracts upper diagonal matrix keeping the values of the diagonal;

    lower=lower`; *transpose the lower diagonal matrix into upper diagonal matrix;
    A=lower+upper; *calculates the sum of the upper diagonal matrix and the transpose of lower diagonal matrix;
    CAR=name`;
    c=name;

    create test_end from A[colname=c];
    append from A;
    close test_end; *creates dataset from the matrix A;

    create test_name var {"CAR"};
    append;
    close test_name; *creates dataset from the column vector of brand names;
    quit;

    *The merged dataset represents an upper diagonal symmetric matix;
    data final;
    merge test_name test_end;
    run;

Joe · Accepted Answer

Transform the data so you have a row for each interaction, then tabulate will do this for you. I assume a maximum of 3 per PID, if that is not the case then increase the size of brands[3].

data have;
input PID decision alternative brand $;
datalines;
1   1   1   BMW
1   0   2   AUDI
1   0   3   Mercedes
2   1   1   AUDI
2   0   2   Mercedes
2   0   3   Land_Rover
3   1   1   Mercedes
3   0   2   BMW
3   0   3   VW
4   1   1   VW
4   0   2   AUDI
5   1   1   BMW
6   1   1   AUDI
6   0   2   VW
6   0   3   VW
7   1   1   Mercedes
7   0   2   AUDI
;;;;
run;

data for_tab;
array brands[3] $ _temporary_ ;
do _n_ = 1 by 1 until (last.PID);
 set have;
 by PID;
 brands[_n_] = brand;
end;
do _t =1  to dim(brands)-1;
 do _u = _t+1 to dim(brands);
  brand_1 = brands[_t];
  brand_2 = brands[_u];
  output;
 end;
end;
keep PID brand_1 brand_2;
call missing(of brands[*]);
run;


proc tabulate data=for_tab;
class brand_1 brand_2;
tables brand_2,brand_1*n;
run;

SAS: to count how many respondents, who have brand i in their choice set, have also brand j in the choice set

Answers (2)

Related Questions