topic Re: PROC REPORT - GROUPING ON MULTIPLE VARIABLES in ODS and Base Reporting

PROC REPORT - GROUPING ON MULTIPLE VARIABLES

DLROW — Thu, 13 Dec 2018 03:42:13 GMT

Hello - In my data set I have 3 columns. I need to group them based first 2 columns and display total How can this be done using PROC REPORT. I have written the code as mentioned below, but observations are not grouped correctly.Thanks for your help.

PROC REPORT DATA = MyData;

COLUMNS region age amount

define region/ group;

define age / group;

break after age/ summarize style={background=lightGrey font_Weight=Bold};

Re: PROC REPORT - GROUPING ON MULTIPLE VARIABLES

singhsahab — Thu, 13 Dec 2018 07:10:32 GMT

Hello Dlrow,

why you are not using region in break statement . I'm posting a simple example where i'm getting expected output. If it's not match your requirement , please share expected output.

data test;
input region $ age amount;
cards;
asian 11 24
asian 12 22
asian 11 21
asian 12 21
asian 11 20
african 10 25
african 10 15
;
run;

PROC REPORT DATA = test;
COLUMNS region age amount;
define region/ group;
define age / group;
define amount/sum;
break after region / summarize style={background=lightGrey font_Weight=Bold};
run;

Thanks 🙂

Re: PROC REPORT - GROUPING ON MULTIPLE VARIABLES

DLROW — Thu, 13 Dec 2018 10:27:45 GMT

That's what I tried but all ages are grouped.. I Want to group ages in each group of region.

Re: PROC REPORT - GROUPING ON MULTIPLE VARIABLES

singhsahab — Thu, 13 Dec 2018 10:37:55 GMT

Can you please share expected output ?

Re: PROC REPORT - GROUPING ON MULTIPLE VARIABLES

ballardw — Thu, 13 Dec 2018 15:29:46 GMT

Input data and expected output will get much better answers. Right now we have to guess what actual output you want.

If your data is sensitive you could recast the problem using a SAS supplied data set such as SASHELP.CLASS. Possibly with Sex instead of Region and height or weight instead of amount (the set does have an age variable).

If you are asking for summary at the region level and a grand total maybe something along the lines of:

PROC REPORT DATA = sashelp.class;
COLUMNS sex age weight;
define sex/ group;
define age / group;
 
break after sex/ summarize style={background=lightGrey font_Weight=Bold};
rbreak after/ summarize ;
run;

If you don't want an age or sex (region) summary remove the break.

But we are guessing what you want because you haven't shown the desired layout.

The missing semicolon on the COLUMNS statement would be an issue.