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 |
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.