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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.