Search code examples
sas

How do I align the output vertically when using PROC REPORT with ACROSS?

I use the following code:

ods excel file="[path].xlsx" options(sheet_interval='bygroup'
                    sheet_name="#byval(bladnamn)");
PROC REPORT data=export;
by bladnamn;
column bladnamn kon, (variabel_cat_varde alder_grupp andel CI_lower CI_upper lillan);
/* define variabelnamn / order order=internal;*/
/*define order / order order=internal;*/
/*define variabel / order order=internal;*/
define bladnamn / noprint;
define variabel_cat_varde / display;
define alder_grupp / display;
define andel / analysis;
define CI_lower / analysis;
define CI_upper / analysis;
define lillan / analysis;
DEFINE kon / ACROSS ORDER=INTERNAL; 



run;

ods excel close;

and get the following output:

enter image description here

That is to say, values for the different values of my gender variable (kon) seem to appear on different rows. Naturally I don't want this to happen. Values belonging to the same group should appear on the same row, regardless of gender.

How do I make this happen?

Solution

  • In order to fix this I had to add "bladnamn" as a grouping variable.

    PROC REPORT data=export;
by bladnamn;
column bladnamn variabel_cat_varde alder_grupp (kon,(andel CI_lower CI_upper lillan));
/* define variabelnamn / order order=internal;*/
define Redovisningsgrupp / group order=data noprint;
define order / order order=internal noprint;
define variabel / order order=internal noprint;
define bladnamn / group order=data noprint;
define variabel_cat_varde / order=data group;
define alder_grupp / order=data group;
define andel / mean;
define CI_lower / mean;
define CI_upper / mean;
define lillan / mean;
DEFINE kon / ACROSS ORDER=INTERNAL;

    (order is set to "data" som my original order isn't messed up)

    Kudos to Tom at communities.sas.com for this answer:

    https://communities.sas.com/t5/SAS-Programming/How-do-I-make-sure-proc-report-with-across-writes-observations/m-p/884813#M349581