Proc report across option creating extra row

[michelconn]

Can anyone tell me why my across variable is displaying data in two rows instead of 1. I created some sample data below and my proc report code.

data sample;
   input timepoint temperature wetdry value1 $ value2 $ value3 $ value4 $;
   datalines;
1 1 1 5 1 3 6
1 1 2 7 5 68 98
1 1 1 156 789 698 12
1 1 2 12 32 54 7
1 2 1 56 89 45 78
1 2 2 135 87 54 12
1 2 1 65 45 7 965
1 2 2 546 45 78 13
2 1 1 5 8 87 65
2 1 2 564 214 487 6
2 1 1 514 8745 46 5 
2 1 2 1 2 5 4
2 2 1 21 54 78 65
2 2 2 32 45 65 78
2 2 1 25 36 845 86
2 2 2 564 781 254 88
;

proc report data=artificial_mean;
	column timepoint temperature value1,wetdry value2,wetdry value3,wetdry value4,wetdry;
	define timepoint / order order=internal;
	define temperature / order order=internal;
	define wetdry / across;
run;

What it looks like

		value1		value2		value3		value4
		wetdry		wetdry		wetdry		wetdry
timepoint	temperature	1	2	1	2	1	2	1	2
1	1	9.1		954.3		179.3		5066.9
			10.6		1032.6		175		5035
2	1	10		966.1		181.1		5039.4
			10.3		959		168.1		5058.6
	3	10.2		1018.6		179.9		5047
			11.3		1012.4		174.1		5046.1
	4	10.6		1039.8		181.4		5067.9
			11.1		995.4		177		5043.8
	5	11.5		1040.4		173		5063
			11.5		1006.6		178.9		5022.3

What I want it too look like

		value1		value2		value3		value4
		wetdry		wetdry		wetdry		wetdry
timepoint	temperature	1	2	1	2	1	2	1	2
1	1	5	7	1	5	3	68	6	98
	2	156	12	789	32	698	54	12	7

[Cynthia_sas · Accepted solution]

Hi:

  Two questions:

1) why are you reading the VALUE1, VALUE2, etc variables as character ($)? PROC REPORT does NOT like to collapse rows for character variables.

2) why do you have both TIMEPOINT and TEMPERATURE as ORDER items? They should be GROUP items. With a usage of ORDER, you are telling PROC REPORT that you want a separate row, as your output shows.

  These two things, combined are preventing you from getting the report that you want. What I recommend is that you

1) take the $ out of the INPUT statement for the VALUEx variables

2) change the usage of TIMEPOINT and TEMPERATURE to be GROUP and not ORDER

This is what I get when I do those 2 things:

Here's the code I used:

data sample;
   input timepoint temperature wetdry value1 value2  value3 value4  ;
   datalines;
1 1 1 5 1 3 6
1 1 2 7 5 68 98
1 1 1 156 789 698 12
1 1 2 12 32 54 7
1 2 1 56 89 45 78
1 2 2 135 87 54 12
1 2 1 65 45 7 965
1 2 2 546 45 78 13
2 1 1 5 8 87 65
2 1 2 564 214 487 6
2 1 1 514 8745 46 5
2 1 2 1 2 5 4
2 2 1 21 54 78 65
2 2 2 32 45 65 78
2 2 1 25 36 845 86
2 2 2 564 781 254 88
;
run;
        
proc report data=sample;
    column timepoint temperature value1,wetdry value2,wetdry value3,wetdry value4,wetdry;
    define timepoint / group order=internal;
    define temperature / group order=internal;
    define wetdry / across;
run;

Hope this helps,

cynthia