BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
J_J_J
Obsidian | Level 7

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Sajid01
Meteorite | Level 14

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

Screenshot 2021-05-11 6.56.29 PM.png

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.

View solution in original post

4 REPLIES 4
ballardw
Super User

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
SAS Super FREQ

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
Meteorite | Level 14

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

Screenshot 2021-05-11 6.56.29 PM.png

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
Obsidian | Level 7

Thank you Sajid01!

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1637 views
  • 2 likes
  • 4 in conversation