SAS in data step reshape a long data into wide with categorical variable

Question

In SAS, a dataset I have is as follows.

id   A

1    2   
1    3 
2    1
3    1
3    2

ID is given to each individual and A is a categorical variable which takes 1, 2 or 3. I want to get the data with one observation per each individual separating A into three indicator variables, say A1, A2 and A3.

The result would look like this:

id A1 A2 A3 

1   0  1  1
2   1  0  0
3   1  1  0

Does anyone have any thought how to do this in data step, not in sql? Thanks in advance.

Answer 1

So you're on the right track, a transpose statement is definitely the way to go:

data temp;
   input id A;
   datalines;
    1 2
    1 3
    2 1
    3 1
    3 2
;
run;

First you want to transpose by id, using the variable A:

proc transpose data = temp
    out = temp2
    prefix = A;
    by id;
    var A;
    id A;
run;

And then, for all variables beginning with A, you want to replace all missing values with 0s and all non-missing values with 1s. The retain statement here reorders your variables:

data temp3 (drop = _name_);
    retain id A1 A2 A3;
    set temp2;
    array change A:;
        do over change;
        if change~=. then change=1;
        if change=. then change=0;
        end;
run;