Offset rows in proc report table

[RobertWF1]

I'm running proc report on a proc freq output table and the resulting table looks good except the rows under the "year" headers are offset (see data example below).

The code is running without errors -- any ideas what's going wrong?

data test;
input year biomarkername $ biomarkerstatus $ count pct_row;
cards;
2017 ALK  N 2716 90.8969
2017 ALK  P 87 2.9116
2017 ALK  U 185 6.1914
2017 BRA  N 1786 92.1569
2017 BRA  P 91 4.6956
2017 BRA  U 61 3.1476
2018 ALK  N 2839 92.3552
2018 ALK  P 72 2.3422
2018 ALK  U 163 5.3025
2018 BRA  N 2364 92.0202
2018 BRA  P 136 5.2939
2018 BRA  U 69 2.6859
;
run;

options missing=' ';
proc report data=test missing;
column biomarkername year,(biomarkerstatus count pct_row);
define biomarkername / group ;
define year / across ;
run;

[ballardw · Accepted solution]

Describe exactly what you mean by "offset row" in that example. As in row number since the data is small it should be easy to count. Row 4 would be the one starting with ALK in biomarkername.

Which values are "offest".

If you want the values of a column centered you will need to specify a style override. Default for SAS is character values are left justified and numbers are right justified unless you specify otherwise.

If want to align values of the BIOMARKERSTATUS then perhaps this makes more sense:

proc report data=test missing;
column biomarkername biomarkerstatus year,( count pct_row);
define biomarkername / group ;
define year / across ;
define biomarkerstatus /group ;

run;

[RobertWF1]

Great, that solved it! Thank you for the quick response.

I see the problem now -- there's no need to include biomarkerstatus with the count and pct_row variables under the year headers since the biomarkerstatus values are repeated in 2017 and 2018.  So better to move biomarkerstatus outside the parentheses in the column statement & include as a separate group variable.

[ballardw]

---

@RobertWF1 wrote:

Great, that solved it! Thank you for the quick response.

I see the problem now -- there's no need to include biomarkerstatus with the count and pct_row variables under the year headers since the biomarkerstatus values are repeated in 2017 and 2018.  So better to move biomarkerstatus outside the parentheses in the column statement & include as a separate group variable.

---

Also if one or more years have different values of biomarkerstatus, such as "not collected yet" or change in values collected the biomarkers will still align within biomarkername and show missing values for the other variables related to that biomarkerstatus value.