Help using Base SAS procedures

cross table with multiple choice

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

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
IDVar1Var2iVar2iiVar2iiiVar2iv
11aac
12b
13bbc
14aabcd
15ac
16bcd
17bbd
18b
19aacd
20ad
Table 2Var2
abcd
Var 1a3143
b0222

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

Re: cross table with multiple choice

Posted in reply to loedrup_ab

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;

View solution in original post


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

Re: cross table with multiple choice

Posted in reply to loedrup_ab

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

Posted in reply to loedrup_ab

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,041

Re: cross table with multiple choice

Posted in reply to loedrup_ab

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 224 views
  • 6 likes
  • 3 in conversation