DATA Step, Macro, Functions and more

Proc report across option creating extra row

Accepted Solution Solved
Reply
Contributor
Posts: 56
Accepted Solution

Proc report across option creating extra row

[ Edited ]

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

Accepted Solutions
Solution
‎03-10-2018 03:26 PM
SAS Super FREQ
Posts: 9,324

Re: Proc report across option creating extra row

Posted in reply to michelconn

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:

collapse_rows.png

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

View solution in original post


All Replies
Solution
‎03-10-2018 03:26 PM
SAS Super FREQ
Posts: 9,324

Re: Proc report across option creating extra row

Posted in reply to michelconn

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:

collapse_rows.png

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 56 views
  • 1 like
  • 2 in conversation