Create transposed map table

[lpy0521]

Hey there,

I have a table look like this:

have:
ID	cat
id 1	Y1
id 2	Y2
…	…
id n	Y m

each id is falling into the corresponding category Y. Do we have any solution to transpose the table such that I can have following:

want:
ID	Y1	Y2	…	Ym
id 1	1	0	…	0
id 2	0	1	…	0
…
id n	0	0	…	1

In the want table, each id is flagged as 1 only if the corresponding column 'Y' can be found from table want.

Thanks in advance!!! 

[PeterClemmensen · Accepted solution]

Not sure if I am over simplifying things here?

data have;
input ID $ cat $;
infile datalines dlm=',';
var=1;
datalines;
id 1, Y1
id 2, Y2
id 3, Y3
;

proc transpose data=have out=temp(drop=_NAME_);
    by ID ;
    id cat;
    var var;
run;

proc stdize data=temp out=want reponly missing=0;
run;

[Reeza]

You can drop the VAR statement, SAS defaults it to 1 then 🙂