Solved
Contributor
Posts: 28

# cross table with multiple choice

Dear anyone

Once again I turn to this site for help.

I have data=want with the variables var1 and var2. Var1 is single choice, either a og b. Var2 is multiple choice, a-d. My data is organized as shown in table 1.

I want to cross-table var1 with var2, as shown in table 2. I have tried proc freq with var1*(var2i-var2iv), but that just gave me four different tables.

I could make a 0/1 variable for each possible answer (a-d), but I have many variables to analyze and it would take a lot of time and typing.

Any suggestions on how to get around this

Sincerely

Anders

 Table 1 ID Var1 Var2i Var2ii Var2iii Var2iv 11 a a c 12 b 13 b b c 14 a a b c d 15 a c 16 b c d 17 b b d 18 b 19 a a c d 20 a d Table 2 Var2 a b c d Var 1 a 3 1 4 3 b 0 2 2 2

Accepted Solutions
Solution
‎10-30-2012 05:20 AM
Regular Contributor
Posts: 151

## Re: cross table with multiple choice

I think the easiest way is to transpose your data so that the values of var2i-var2iv are all in 1 column.  Doing the crosstab after that is then simple.

data have;

infile cards missover;

input id var1 \$ var2i \$ var2ii \$ var2iii \$ var2iv \$;

cards;

11 a a c

12 b

13 b b c

14 a a b c d

15 a c

16 b c d

17 b b d

18 b

19 a a c d

20 a d

;

run;

data have_trans;

set have;

array trans_var{*} var2: ;

do i=1 to dim(trans_var);

if missing(trans_var{i}) then leave;

new_var=trans_var{i};

output;

end;

drop var2: i;

run;

proc freq data=have_trans;

table var1*new_var / nopercent norow nocol;

run;

All Replies
Solution
‎10-30-2012 05:20 AM
Regular Contributor
Posts: 151

## Re: cross table with multiple choice

I think the easiest way is to transpose your data so that the values of var2i-var2iv are all in 1 column.  Doing the crosstab after that is then simple.

data have;

infile cards missover;

input id var1 \$ var2i \$ var2ii \$ var2iii \$ var2iv \$;

cards;

11 a a c

12 b

13 b b c

14 a a b c d

15 a c

16 b c d

17 b b d

18 b

19 a a c d

20 a d

;

run;

data have_trans;

set have;

array trans_var{*} var2: ;

do i=1 to dim(trans_var);

if missing(trans_var{i}) then leave;

new_var=trans_var{i};

output;

end;

drop var2: i;

run;

proc freq data=have_trans;

table var1*new_var / nopercent norow nocol;

run;

Contributor
Posts: 28

## Re: cross table with multiple choice

Hi Keith

Awesome :-) I had a notion of transposing would be the right thing to do - but I had no idea of how to do it. You obviously had :-) Only, I had to put in a proc sort statement, so I ended up doing:.

data have;

infile cards missover;

input id var1 \$ var2i \$ var2ii \$ var2iii \$ var2iv \$;

cards;

11 a a c

12 b

13 b b c

14 a a b c d

15 a c

16 b c d

17 b b d

18 b

19 a a c d

20 a d

;

run;

proc sort data=have; by id; run;

data have_trans;

set have;

by id;

array trans_var{*} var2: ;

do i=1 to dim(trans_var);

if missing(trans_var{i}) then leave;

new_var=trans_var{i};

output;

end;

drop var2: i;

run;

Thanks a 10^6

Sincerely

Anders

Regular Contributor
Posts: 151

## Re: cross table with multiple choice

Glad to be of help. I actually made a small modification to my original code, the 'by ID' line in the data step is not strictly necessary so I took it out.  Then you wouldn't then to pre-sort the dataset.

Super User
Posts: 10,788

## Re: cross table with multiple choice

Or you could try a SQL way which don't need to transpose dataset.

If there were many variables, you can use a macro variable to wrap them all.

```data have;
infile cards missover;
input id var1 \$ var2i \$ var2ii \$ var2iii \$ var2iv \$;
cards;
11 a a c
12 b
13 b b c
14 a a b c d
15 a c
16 b c d
17 b b d
18 b
19 a a c d
20 a d
;
run;
proc sql;
create table want as
select var1,sum(var2i='a')+sum(var2ii='a')+sum(var2iii='a')+sum(var2iv='a') as a,
sum(var2i='b')+sum(var2ii='b')+sum(var2iii='b')+sum(var2iv='b') as b,
sum(var2i='c')+sum(var2ii='c')+sum(var2iii='c')+sum(var2iv='c') as c,
sum(var2i='d')+sum(var2ii='d')+sum(var2iii='d')+sum(var2iv='d') as d
from have
group by var1;
quit;

```

Ksharp

🔒 This topic is solved and locked.