topic Re: Multiple group by rules int he same Proc Sql? in SAS Programming

Multiple group by rules int he same Proc Sql?

Cruise — Fri, 08 Feb 2019 18:29:30 GMT

HI folks,

I'm using proc sql to summarize my data for the plot grouping by the WGT-DX and N of Month variable. This works well for the plotting data. Also, the total number of patients by (WGT_DX) variable which has 6 levels needs to be shown in each panel of the plot. I used INSERT function in the following proc sgpanel.

Is there a way to use different grouping rules in the same PROC SQL? I mean, use "GROUP BY N_MONTH_ENROLLED, WGT_DX" for the "MEANS_DAYS" while using "GROUP BY WGT_DX for the COUNT(DISPLAY_ID) to create a variable highlighted in red under N_PATIENTS int he image?

DATA HAVE; 
INPUT N_MONTH_ENROLLED WGT_DX MEAN_DAYS N_PATIENTS;
CARDS;
1 1 17.358 201 
1 2 29.000 1 
1 3 57.000 8 
1 4 139.200 5 
1 5 1.000 1 
1 6 49.667 6 
2 1 22.213 253 
2 2 73.500 2 
2 3 48.400 5 
2 4 88.333 3 
2 5 8.000 2 
2 6 132.000 1 
3 1 27.398 274 
3 2 0.000 2 
3 3 50.500 2 
3 4 0.000 1 
3 6 41.400 5 
4 1 26.337 273 
4 2 143.000 2 
4 3 28.667 6 
4 4 57.571 7 
4 5 0.000 1 
4 6 53.750 4 
5 1 24.623 281 
5 2 0.000 1 
5 3 45.000 4 
5 4 149.000 4 
5 5 7.000 1 
5 6 3.500 4 
6 1 22.702 309 
6 2 152.000 1 
6 3 12.000 2 
6 4 69.500 6 
6 6 72.000 1 
7 1 19.066 317 
7 2 0.000 1 
7 3 0.000 1 
7 4 12.333 3 
7 5 0.000 3 
7 6 27.400 5 
8 1 24.244 246 
8 2 8.500 4 
8 3 18.200 5 
8 4 54.500 4 
8 5 14.000 1 
8 6 16.000 2 
9 1 17.701 271 
9 2 7.000 1 
9 3 48.167 6 
9 4 65.500 8 
9 6 0.500 4 
10 1 20.129 319 
10 2 0.000 1 
10 3 56.571 7 
10 4 48.125 8 
10 5 5.500 2 
10 6 86.400 5 
11 1 14.134 329 
11 2 11.500 6 
11 3 7.400 5 
11 4 72.579 19 
11 5 0.000 3 
11 6 0.143 7 
12 1 11.216 3251 
12 2 13.246 69 
12 3 18.892 93 
12 4 65.955 156 
12 5 15.806 36 
12 6 22.512 41 
;

PROC SQL;
CREATE TABLE HAVE AS
SELECT N_MONTH_ENROLLED, WGT_DX,
MEAN(DOD_DIFF) AS MEAN_DAYS, COUNT(DISPLAY_ID) AS N_PATIENTS
FROM HAVE1
GROUP BY N_MONTH_ENROLLED,WGT_DX;
QUIT; 

ods graphics / height=400px width=800px;
TITLE "hello";
proc sgpanel data=have;
panelby wgt_dx/onepanel novarname ROWS=3;
series x=N_MONTH_ENROLLED  y=MEAN_DAYS / markerattrs=(size=3px);
INSET N_PATIENTS/position=top;
colaxis label='LABEL' fitpolicy=thin valuesformat=best2.0 values=(1 to 12 by 1);;
rowaxis label='LABEL' grid; 
TITLE "MYTITLE";
run;

Re: Multiple group by rules int he same Proc Sql?

PaigeMiller — Fri, 08 Feb 2019 18:44:37 GMT

Is there a way to use different grouping rules in the same PROC SQL? I mean, use "GROUP BY N_MONTH_ENROLLED, WGT_DX" for the "MEANS_DAYS" while using "GROUP BY WGT_DX for the COUNT(DISPLAY_ID) to create a variable highlighted in red under N_PATIENTS int he image?

This is exactly what PROC SUMMARY does.

Re: Multiple group by rules int he same Proc Sql?

Cruise — Fri, 08 Feb 2019 18:56:50 GMT

proc summary with multiple class statements?

Re: Multiple group by rules int he same Proc Sql?

PaigeMiller — Fri, 08 Feb 2019 19:09:25 GMT

@Cruise wrote:
proc summary with multiple class statements?

PROC SUMMARY with multiple class variables (and perhaps a TYPES or WAYS statement)

If I want to take the SASHELP.CLASS and compute mean height and weight by SEX, and also by SEX * AGE, it looks like this:

proc summary data=sashelp.class;
    class sex age;
    types sex sex*age;
    var height weight;
    output out=results mean=;
run;

Re: Multiple group by rules int he same Proc Sql?

Cruise — Fri, 08 Feb 2019 19:19:12 GMT

I see ,I want mean of height by sex and age and
n of counts of rows by sex only. Any suggestions?

Re: Multiple group by rules int he same Proc Sql?

PaigeMiller — Fri, 08 Feb 2019 19:41:06 GMT

output out=results mean= n= /autoname;

Re: Multiple group by rules int he same Proc Sql?

PGStats — Sat, 09 Feb 2019 05:37:05 GMT

Or just summarize one more level

PROC SQL;
CREATE TABLE HAVE AS
select 
    *, 
    sum(n_patients) as total_patients
from
    (
    SELECT  
        N_MONTH_ENROLLED, 
        WGT_DX,
        MEAN(DOD_DIFF) AS MEAN_DAYS, 
        COUNT(DISPLAY_ID) AS N_PATIENTS
    FROM HAVE1
    GROUP BY N_MONTH_ENROLLED, WGT_DX 
    )
group by wgt_dx;
QUIT;

(untested)