Reordering columns after PROC TRANSPOSE

[J_J_J]

Hi!

I'm trying to find the way to sort data after PROC TRANSPOSE automatically

I have data:

data have;
month=1; code='AA1111111'; text='aaa'; prior='2'; type='BB'; num=4; output;
month=1; code='BB1111111'; text='bbb'; prior='4'; type='AA'; num=8; output;
month=1; code='BB1111111'; text='bbb'; prior='4'; type='BB'; num=1; output;
month=1; code='CC1111111'; text='ccc'; prior='4'; type='CC'; num=5134; output;
month=1; code='CC1111111'; text='ccc'; prior='4'; type='DD'; num=525; output;
month=1; code='CC1111111'; text='ccc'; prior='4'; type='EE'; num=125; output;
month=2; code='BB1111111'; text='bbb'; prior='4'; type='AA'; num=4; output;
month=2; code='CC1111111'; text='ccc'; prior='4'; type='CC'; num=5321; output;
month=2; code='CC1111111'; text='ccc'; prior='4'; type='DD'; num=256; output;
month=2; code='CC1111111'; text='ccc'; prior='4'; type='EE'; num=151; output;
month=3; code='BB1111111'; text='bbb'; prior='4'; type='AA'; num=12; output;
month=3; code='CC1111111'; text='ccc'; prior='4'; type='CC'; num=2860; output;
month=3; code='CC1111111'; text='ccc'; prior='4'; type='DD'; num=655; output;
month=3; code='CC1111111'; text='ccc'; prior='4'; type='EE'; num=62; output;
month=4; code='AA1111111'; text='aaa'; prior='2'; type='AA'; num=2; output;
month=4; code='BB1111111'; text='bbb'; prior='4'; type='AA'; num=12; output;
month=4; code='CC1111111'; text='ccc'; prior='4'; type='CC'; num=1962; output;
month=4; code='CC1111111'; text='ccc'; prior='4'; type='DD'; num=865; output;
month=4; code='CC1111111'; text='ccc'; prior='4'; type='EE'; num=112; output;
run;
PROC SORT
DATA=have(KEEP= month num code text prior type)
OUT=sorted
;
BY code text prior type;
RUN;

PROC TRANSPOSE DATA=sorted
OUT=want(LABEL="Transposed WORK.WANT")
NAME=Source
LABEL=Label

;
BY code text prior type ;
ID month ;
VAR num;

RUN; QUIT;

and get the result:

code	text	prior	type	Source	4	1	2	3
AA1111111	aaa	2	AA	num	2
AA1111111	aaa	2	BB	num		4
BB1111111	bbb	4	AA	num	12	8	4	12
BB1111111	bbb	4	BB	num		1
CC1111111	ccc	4	CC	num	1962	5134	5321	2860
CC1111111	ccc	4	DD	num	865	525	256	655
CC1111111	ccc	4	EE	num	112	125	151	62

but I need it in the next order and with total:

code	text	prior	type	Source	1	2	3	4	Total
AA1111111	aaa	2	AA	num				2	2
AA1111111	aaa	2	BB	num	4				4
BB1111111	bbb	4	AA	num	8	4	12	12	36
BB1111111	bbb	4	BB	num	1				1
CC1111111	ccc	4	CC	num	5134	5321	2860	1962	15277
CC1111111	ccc	4	DD	num	525	256	655	865	2301
CC1111111	ccc	4	EE	num	125	151	62	112	450

Each month the number of columns will change from 1 to 12 and should be in the order from 1 to 12 (from left to right) and total should be calculated for filled months.

Can you help me please?

[ballardw]

First question: do you really need a data set? When people worry about the order of variables and include any sort of "TOTAL" that means that people are reading the data and that typically means a "report" is more in order. Report procedures like Report or Tabulate will pay much  more attention to "order" as that is often an option in the syntax than Proc Transpose, which takes the first value encountered.

One possible way:

Proc tabulate data=have;
   class code month text prior type;
   var num;
   table code*	text*prior*	type,
         (month All='Total')*num=''*sum=''*f=best8.
         /misstext=' '
   ;
run;

[sbxkoenk]

Hello,

I absolutely agree with @ballardw .

Unfortunately, if you really need an output table, you cannot use ODS OUTPUT TABLE= on PROC TABULATE. Well, you can, but the table will not be transposed anymore 😕😕.

For re-ordering columns, see here:

Usage Note 8395: How to reorder the variables in a SAS® data set?

https://support.sas.com/kb/8/395.html

Koen

[Sajid01 · Accepted solution]

Hello
The following code does what you wanted.

option validvarname=ANY;
data HOIAT_ARV2;
Retain code text prior type Source  '1'n '2'n '3'n '4'n;
set HOIAT_ARV;
TOTAl=sum(of '1'n -'4'n);
run;

Your output will be

While one may be able to use numbers as column headings / variable names, but that is not the default, Better to have a more meaningful name instead of 1 2 3 4 etc.

[J_J_J]

Thank you Sajid01!