topic Re: Proc transpose to show months by ID in SAS Programming

Proc transpose to show months by ID

CatM — Tue, 03 Nov 2020 00:48:35 GMT

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!

Re: Proc transpose to show months by ID

novinosrin — Tue, 03 Nov 2020 01:06:45 GMT


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;

Re: Proc transpose to show months by ID

Ksharp — Tue, 03 Nov 2020 12:46:04 GMT

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;

Re: Proc transpose to show months by ID

Kurt_Bremser — Tue, 03 Nov 2020 13:35:02 GMT

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;

Re: Proc transpose to show months by ID

CatM — Tue, 03 Nov 2020 16:29:15 GMT

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.

Re: Proc transpose to show months by ID

CatM — Tue, 03 Nov 2020 16:31:26 GMT

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!