The SAS Output Delivery System and reporting techniques

Proc Report - "Transposing and Collapsing a Column"

Reply
Contributor krm
Contributor
Posts: 26

Proc Report - "Transposing and Collapsing a Column"

[ Edited ]

Hi, 

 

I have the following data set.

 

 

data teams;
input
team $5. wins group $5.;
format
team $5.;
datalines;
TeamA 10 West
TeamA 15 West
TeamA 32 West
TeamB 2  West
TeamB 17 West
TeamB 5  West
;

 

 

I am using proc report to transpose the team names and grouping them by group. I am using the following code below. 

 

 

proc report nowd data=teams
style(header)=[background=white];
col (group (('Teams' team),(wins)));
	define group/   "Group";
	define team /  across style (header)=[background=lightblue] ' ' ; 
	define wins /  "Wins";
run;

 

 

When I run the code I get the following result below:

 

West10.
West15.
West32.
West.2
West.17
West.5

 

 

I am trying to figure out what I am missing here, I want to group both teams by group, so that the end result would look like below. 

 


West102
West1517
West325


Any help would be greatly appreciated!

Thanks!!

 

 

Edited:

 

I was able to figure it out later and wanted to keep to post for others if further reference is needed. 

 

Adding group in the line solves the problem 

 

proc report nowd data=teams
style(header)=[background=white];
col (group division (('Teams' team),(wins)));
	define group/ group  "Group";
	define division/ group  "Div";
	define team /  across style (header)=[background=lightblue] ' ' ; 
	define wins /  "Wins";
run;
Grand Advisor
Posts: 9,325

Re: Proc Report - "Transposing and Collapsing a Column"

You need to change data set.

 

data teams;
input team $5. wins group $5.;
format
team $5.;
datalines;
TeamA 10 West
TeamA 15 West
TeamA 32 West
TeamB 2  West
TeamB 17 West
TeamB 5  West
;
run;
data teams;
 set teams;
 by group team;
 if first.team then n=0;
 n+1;
run;
proc report nowd data=teams
style(header)=[background=white];
col group n ('Teams' team),wins;
	define group/group   "Group";
	define n/group noprint;
	define team /  across style (header)=[background=lightblue] ' ' ; 
	define wins /  "Wins";
run;
Super Contributor
Posts: 396

Re: Proc Report - "Transposing and Collapsing a Column"

Could you please tell me the purpose of folloing line in proc report?

 

col group n ('Teams' team),wins;
Grand Advisor
Posts: 9,325

Re: Proc Report - "Transposing and Collapsing a Column"

I made another group variable N to make it happen.

You will see N is how to generate in data step.

Post a Question
Discussion Stats
  • 3 replies
  • 370 views
  • 1 like
  • 3 in conversation