BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Syntas_error
Quartz | Level 8

 

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:

Namnlös.png

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?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

define bladnamn / group noprint;

View solution in original post

13 REPLIES 13
Syntas_error
Quartz | Level 8

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;

 

Syntas_error
Quartz | Level 8

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
Quartz | Level 8
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
Super User Tom
Super User

@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
Super User
/*
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
Quartz | Level 8
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
Super User Tom
Super User

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

define bladnamn / group noprint;
Syntas_error
Quartz | Level 8
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
Super User Tom
Super User

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
Quartz | Level 8
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
Super User Tom
Super User

Then tell PROC REPORT how to order the grouping variable.

To leave them the same then try ORDER=DATA .

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 2176 views
  • 3 likes
  • 4 in conversation