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

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
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
Diamond | Level 26

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

1 REPLY 1
Cynthia_sas
Diamond | Level 26

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 1 reply
  • 1178 views
  • 2 likes
  • 2 in conversation