How do I make sure proc report with across writes observations to the same row?

[Syntas_error]

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:

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?

[Kurt_Bremser]

You need a column defined as GROUP which forces multiple values of kon into the same row.

[Syntas_error]

Something like this, or should I avoid using multiple group variables?

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


run;

[Kurt_Bremser]

You can define as many variables as GROUP as you like; it has to make sense for your intended logic.

[Syntas_error]

That may be true, but I posted that snippet (inspired by this page https://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf) mostly because it didn't work (the results are still written to different rows according to sex) and I'm trying to wrap my head around why. 

I asked my manager about posting a repex and hopefully it shouldn't be a problem. 

[Syntas_error]

Okay, uploaded data for one of the sheets, should be enough I hope. On that note, is there a preferred way to upload repredocible examples for sas?

[Tom]

---

@Syntas_error wrote:
Okay, uploaded data for one of the sheets, should be enough I hope. On that note, is there a preferred way to upload repredocible examples for sas?

---

Just share the data as an actual data step.

data class ;
  input name :$8. sex :$1. age height weight ;
cards;
Alfred M 14 69 112.5
Alice F 13 56.5 84
Barbara F 13 65.3 98
Carol F 14 62.8 102.5
Henry M 14 63.5 102.5
James M 12 57.3 83
Jane F 12 59.8 84.5
Janet F 15 62.5 112.5
Jeffrey M 13 62.5 84
John M 12 59 99.5
Joyce F 11 51.3 50.5
Judy F 14 64.3 90
Louise F 12 56.3 77
Mary F 15 66.5 112
Philip M 16 72 150
Robert M 12 64.8 128
Ronald M 15 67 133
Thomas M 11 57.5 85
William M 15 66.5 112
;

Which is trivial to create from an existing dataset.  Just use a data step to dump the lines of data to the SAS log.

data _null_;
  set sashelp.class ;
  put name sex age height weight;
run;

You can copy and paste them back into the program editor.

If your data is more complicated there a macros that can create the data step for you.  Look at https://github.com/sasutils/macros/blob/master/ds2post.sas

[Ksharp]

/*
Here is an example .
You need a ID variable.
*/
data have;
 set sashelp.heart(obs=20);
 keep status sex ageatstart weight height;
run;
proc sort data=have;by status sex;run;
data have;
 set have;
 by status sex;
 if first.sex then id=0;
 id+1;
run;
proc report data=have nowd;
by status;
column id sex,(ageatstart weight height);
define id/group noprint;
define sex/across '';
define ageatstart/display;
run;

[Syntas_error]

I'm sorry but I have a hard time using code I don't understand. What does ID do exactly? And would I be able to use this method with two additional character variables besides the gender variable used for ACROSS?

[Tom · Accepted solution]

Try letting PROC REPORT know that you want one row per value of BLADNAMN.

define bladnamn / group noprint;

[Syntas_error]

Thanks, that worked!

The order is a bit distorted (I guess the observations get shuffled arund during the pivoting process?) but strictly speaking that's another question 🙂

[Tom]

You told it to order the columns based on the stored value of KON (not the displayed values).

DEFINE kon / ACROSS ORDER=INTERNAL; 

[Syntas_error]

Sure, but that's for telling SAS the order in which the pivoted columns should be presented ?

(so "1!" to the far left, "2" in the middle and "3" to the far right)

That I have no problem with, only that the order of rows seems to have changed from the order in the original data.

[Tom]

Then tell PROC REPORT how to order the grouping variable.

To leave them the same then try ORDER=DATA .