Proc transpose to show months by ID

[CatM]

Hello everyone, 

I have been looking for some way to transpose data that contains which months certain individuals participated and how this can be turned into a readable table. 

the data is currently like this

ID       Month

01      JUN

02      MAY

02      JUN

03      APR

03      MAY

03      JUL

03      AUG

04      AUG

04      SEP

04      OCT

and i want it to look like this:

ID       APR     MAY    JUN    JUL    AUG     SEP       OCT

01                                X

02                      X       X

03          X         X                   X        X

04                                                      X           X             X

Is this possible with proc transpose or with some other proc in SAS? 

thanks!

[novinosrin · Accepted solution]

data have;
input ID $      Month $;
cards;
01      JUN

02      MAY

02      JUN

03      APR

03      MAY

03      JUL

03      AUG

04      AUG

04      SEP

04      OCT
;

data want;
 do until(last.id);
  set have;
  by id;
  array t $ APR     MAY    JUN    JUL    AUG     SEP  OCT;
  array u(7)$ _temporary_ ('APR'   'MAY'   'JUN'  'JUL'  'AUG' 'SEP'  'OCT') ;
  _n_=whichc(month,of u(*));
  if _n_ then t(_n_)='x';
 end;
run;

[CatM]

thanks, this worked beautifully for the visual. I tried the other two solutions but I still get the same errors I got when trying to transpose so this seems like the best option. 

[Ksharp]

data have;
input ID $      Month $;
cards;
01      JUN
02      MAY
02      JUN
03      APR
03      MAY
03      JUL
03      AUG
04      AUG
04      SEP
04      OCT
;
data have;
 set have;
 value=1;
run;
proc transpose data=have out=temp(drop=_name_);
by id;
var value;
id month;
run;
proc stdize data=temp out=temp1 missing=0 reponly;
var _numeric_;
run;
data want;
retain ID  APR     MAY    JUN    JUL    AUG     SEP       OCT;
set temp1;
run;

[CatM]

Thanks, this is what I was initially attempting but I keep getting an error either in the BY or ID group that says the value appears more than once in the column of data. not sure if there is a work around or if Proc Transpose just isn't set up for duplicate values to be organized in a table like I would like. 

Thanks!

[Kurt_Bremser]

What if the data stretches over more than one year?

Anyway, add a template for the months:

data have;
input ID $ Month $;
datalines;
01      JUN
02      MAY
02      JUN
03      APR
03      MAY
03      JUL
03      AUG
04      AUG
04      SEP
04      OCT
;

data have2;
if 0 then set have;
if _n_ = 1
then do;
  month = 'JAN';
  output;
  month = 'FEB';
  output;
  month = 'MAR';
  output;
  month = 'APR';
  output;
  month = 'MAY';
  output;
  month = 'JUN';
  output;
  month = 'JUL';
  output;
  month = 'AUG';
  output;
  month = 'SEP';
  output;
  month = 'OCT';
  output;
  month = 'NOV';
  output;
  month = 'DEC';
  output;
end;
value = "X";
set have;
output;
run;

proc transpose
  data=have2
  out=want (drop=_name_ where=(id ne ""))
;
by id;
var value;
id month;
run;