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?
You need a column defined as GROUP which forces multiple values of kon into the same row.
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;
You can define as many variables as GROUP as you like; it has to make sense for your intended logic.
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.
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
/* 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;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.