topic Re: Reordering columns after PROC TRANSPOSE in SAS Procedures

Reordering columns after PROC TRANSPOSE

J_J_J — Wed, 12 May 2021 05:18:00 GMT

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?

Re: Reordering columns after PROC TRANSPOSE

ballardw — Tue, 11 May 2021 14:57:56 GMT

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;

Re: Reordering columns after PROC TRANSPOSE

sbxkoenk — Tue, 11 May 2021 16:41:56 GMT

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

Re: Reordering columns after PROC TRANSPOSE

Sajid01 — Tue, 11 May 2021 22:59:26 GMT

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.

Re: Reordering columns after PROC TRANSPOSE

J_J_J — Wed, 12 May 2021 05:26:00 GMT

Thank you Sajid01!