topic Re: Proc Report - "Transposing and Collapsing a Column" in ODS and Base Reporting

Proc Report - "Transposing and Collapsing a Column"

krm — Fri, 22 Jan 2016 20:09:04 GMT

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:

West	10	.
West	15	.
West	32	.
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.

West	10	2
West	15	17
West	32	5

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;

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

Ksharp — Mon, 25 Jan 2016 02:52:52 GMT

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;

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

Babloo — Mon, 25 Jan 2016 09:19:10 GMT

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

col group n ('Teams' team),wins;

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

Ksharp — Mon, 25 Jan 2016 09:45:03 GMT

I made another group variable N to make it happen.

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