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;
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)
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.
@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;
output out=results mean= n= /autoname;
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)
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!
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.