topic Transpose data in SAS Programming

Transpose data

DeepakSwain — Tue, 31 Jan 2017 17:19:06 GMT

Hi there,

I am trying to crreate a table having summary of report submitted in each month and experiencing some problem. Can somebody help me to overcome the obstacle. Sample of the table, I am havcing as well as I am expecting are given below. Thank you in advance for your kind reply.

data have;
input id mon $ count ;
cards;
101 jan 10
101 feb 20
101 mar 25
101 jun 20
102 jan 10
102 feb 20
102 Apr 25
102 may 20
103 Apr 10
103 feb 20
103 mar 25
103 may 20
103 jul 25
;
run;


data want;
input id jan feb mar apr may jun jul;
cards;
101 10 20 25 . . 20 .
102 10 20 . 25 20 . .
103 . 20 25 10 20 . 25
;
run;

Regards,

Re: Transpose data

Astounding — Tue, 31 Jan 2017 17:45:45 GMT

Often, a good tool to transpose data is PROC TRANSPOSE:

proc transpose data=have out=want (drop=_name_);

var count;

id mon;

by id;

run;

The documentation describes the purpose of each of the statements.

Re: Transpose data

ballardw — Tue, 31 Jan 2017 18:45:17 GMT

Of course if you are generating a report then perhaps a report procedure is in order.

proc report data=have nowd;
   column id (mon, count);
   define id/group;
   define mon/across;
   define count/analysis ;
run;

Re: Transpose data

DeepakSwain — Tue, 31 Jan 2017 20:56:16 GMT

Thank you for your valuable reply.

Regards,

Re: Transpose data

DeepakSwain — Tue, 31 Jan 2017 20:57:06 GMT

Thank you for providing an alternative approach. Appreciated.

Regards,