Equivalent of Crosstab Access request in SAS?

Question

Here is my input:

ID color
1 green
1 red
1 orange
1 green
1 red
2 red
2 red
2 blue
3 green
3 red

Here is what I want in my release - the number of entries by ID for each color:

Id green red orange blue
1 2 2 1 0
2 0 2 0 1
3 1 1 0 0

I know that I can get information using proc freq, but I want to output the data set in the same way as the one I wrote above. I can't figure out how to make colors columns in this output dataset.

Answer 1

generate data first.

data data;
    format ID 8. Color $8.;
    input id color;
datalines;
1   green
1   red
1   orange
1   green
1   red
2   red
2   red
2   blue
3   green
3   red
run;

Next, we summarize the color count by id.

proc freq data=data noprint;
    table id*color / out=freq;
run;

make the table flat.

proc transpose data=freq out=freq_trans(drop=_:);
    id color;
    by id;
    var count;
run;

optionally fill in the missing cells 0.

data freq_trans_filled;
    set freq_trans;
    array c(*) green red orange blue;
    do i = 1 to dim(c);
        if c[i]=. then c[i]=0;
    end;
    drop i;
run;

Answer 2

, SPARSE PROC FREQ TABLE. , DATA. ORDER= PROC FREQ.

data one;
  input id color :$8.;
datalines;
1   green
1   red
1   orange
1   green
1   red
2   red
2   red
2   blue
3   green
3   red
run;
proc freq data=one noprint order=data;
  table id*color /out=freq sparse;
run;
proc transpose data=freq out=two(drop=_:);
    id color;
    by id;
    var count;
run;
proc print data=two noobs;
run;
/* on lst
id    green    red    orange    blue
 1      2       2        1        0
 2      0       2        0        1
 3      1       1        0        0
*/

Answer 3

I have never been a fan of proc transpose because I can never remember the syntax. Here you can do this using proc sql and a macro variable.

proc sql noprint;  
    select    sum(color = '" || trim(color) || "') as " || color into: color_list separated by ", "
    from      (select distinct color from one);  
    create table result as  
    select    id,  
              &color_list  
    from      one  
    group by  id;  
quit;



id            blue        green         orange            red  
1              0              2              1              2  
2              1              0              0              2  
3              0              1              0              1  

Answer 4

For (pteranodon) I had to revise the archives (after 6+ years), so this is untimely, but someone might be useful.

    proc sql noprint feedback;  
     select    catx(' ','sum(color =',quote(trim(color)),') as',color) into: color_list separated by ", "
       from (select distinct color from one);  
     create table result as  
      select id, &color_list  
      from one 
      group by id;  
    quit;