Summarise: PROC FREQ, PROC MEANS and PROC SQL

Every program in this book looks at data, counts and summarises. Four procedures do most of that work: PROC PRINT, PROC FREQ, PROC MEANS and PROC SQL. This chapter teaches them on ADSL. In one real study, 135 of 276 programs used PROC SQL. Use the procedures to count and summarise. Use the DATA step for logic that depends on the order of records.

6.1 Look at a data set: CONTENTS, PRINT, TITLE

/* Chapter 6: look at a data set with PROC CONTENTS and PROC PRINT */

/* the variables: name, type, length, format and label, in data set order */
TITLE1 "ADSL: the first ten variables";
/* ODS SELECT keeps two of the tables: the data set facts and the variables */
ODS SELECT Attributes Position;
PROC CONTENTS DATA=refadm.adsl(KEEP=studyid--trt01an) VARNUM;
RUN;

/* five records, with labels as column headers */
TITLE1 "ADSL: the first five subjects of the safety population";
FOOTNOTE1 "Source: refadm.adsl";
PROC PRINT DATA=refadm.adsl(OBS=5) LABEL NOOBS;
  WHERE saffl = "Y";
  ID usubjid;
  VAR trt01a age sex race;
RUN;

/* switch the titles and footnotes off again */
TITLE;
FOOTNOTE;

What you should see: first the facts of the data set (254 observations, 10 variables, because KEEP= kept ten), then the variables in data set order:

 #    Variable    Type    Len    Label
 1    STUDYID     Char     12    Study Identifier
 2    USUBJID     Char     11    Unique Subject Identifier
 ...
 9    TRT01A      Char     20    Actual Treatment for Period 01
10    TRT01AN     Num       8    Actual Treatment for Period 01 (N)

Then five subjects, with the labels as column headers:

  Unique
  Subject      Actual Treatment
Identifier     for Period 01           Age    Sex    Race

01-701-1015    Placebo                  63     F     WHITE
01-701-1023    Placebo                  64     M     WHITE
01-701-1028    Xanomeline High Dose     71     M     WHITE
01-701-1033    Xanomeline Low Dose      74     M     WHITE
01-701-1034    Xanomeline High Dose     77     F     WHITE

studyid--trt01an is a variable range: every variable from STUDYID to TRT01AN in data set order.
VARNUM lists the variables in data set order. Without it, the list is alphabetical.
ODS, the Output Delivery System, is the part of SAS that sends the output of a procedure to the window, to a file or to a data set. ODS SELECT keeps only the output tables that you name. PROC CONTENTS also prints a table with the path of the file. You do not need it.
OBS=5 stops after five records. With a WHERE, they are the first five records that meet the condition.
ID usubjid prints the subject first, on the left. VAR gives the other columns in the order you want. LABEL uses the labels as headers. NOOBS drops the observation number.
TITLE1 and FOOTNOTE1 stay until you change them. TITLE; and FOOTNOTE; with nothing after them switch them off. Always switch them off at the end, or the next output has the wrong title.

Exercise 6-1. Print the first five subjects of the placebo arm only. Which WHERE do you need, and who is the fifth subject?

6.2 Count: PROC FREQ

/* Chapter 6: count with PROC FREQ */
TITLE1 "Age group (safety population)";
PROC FREQ DATA=refadm.adsl;
  WHERE saffl = "Y";
  TABLES agegr1 / NOCUM;
  TABLES trt01a * agegr1 / NOROW NOPERCENT;
RUN;

/* LIST gives one row per combination; OUT= keeps the counts as data */
TITLE1 "Treatment and sex, one row per combination";
PROC FREQ DATA=refadm.adsl;
  WHERE saffl = "Y";
  TABLES trt01a * sex / LIST NOCUM OUT=trt_sex;
RUN;

/* a missing value is not counted, unless you ask for it with MISSING */
/* VSTPT (the position) is blank for weight and temperature */
TITLE1 "Position of the BASELINE vital signs: without and with MISSING";
PROC FREQ DATA=sdtm.vs;
  WHERE visit = "BASELINE";
  TABLES vstpt / NOCUM;
  TABLES vstpt / NOCUM MISSING;
RUN;
TITLE;

What you should see: age groups 65-80 144, <65 33, >80 77. Then the same groups by treatment:

Frequency        |
Col Pct          |65-80   |<65     |>80     |  Total
-----------------+--------+--------+--------+
Placebo          |     42 |     14 |     30 |     86
                 |  29.17 |  42.42 |  38.96 |
-----------------+--------+--------+--------+
Xanomeline High  |     55 |     11 |     18 |     84
Dose             |  38.19 |  33.33 |  23.38 |
-----------------+--------+--------+--------+
Xanomeline Low D |     47 |      8 |     29 |     84
ose              |  32.64 |  24.24 |  37.66 |
-----------------+--------+--------+--------+
Total                 144       33       77      254

Then one row per treatment and sex (Placebo F 53, M 33; High Dose F 40, M 44; Low Dose F 50, M 34), and at the end the positions of the BASELINE vital signs, twice.

TABLES a * b makes a cross table. NOROW and NOPERCENT remove the row and total percents, so each cell shows the count and the column percent.
LIST writes one row per combination instead of a cross table. It is easier to read, and OUT= keeps the same counts as a data set for the next step.
A missing value is not counted. Without MISSING, the last table shows three positions with 759 records each, and a line Frequency Missing = 506: weight and temperature have no position. With MISSING, the 506 records get their own row, and the percents change from 33.33 to 27.27. Decide on purpose which one your table needs.
The text categories sort as text: 65-80 comes before <65. To get another order, sort by a number, as chapter 13 does with AGEGR1N.

Exercise 6-2. Count RACE by treatment in the safety population with LIST. How many White subjects are in the placebo arm?

6.3 Statistics: PROC MEANS, PROC SUMMARY, PROC UNIVARIATE

/* Chapter 6: statistics with PROC MEANS, PROC SUMMARY and PROC UNIVARIATE */
TITLE1 "Age by treatment (safety population)";
PROC MEANS DATA=refadm.adsl N MEAN STD MEDIAN MIN MAX MAXDEC=1;
  WHERE saffl = "Y";
  CLASS trt01a;
  VAR age;
RUN;

/* the same statistics as data: PROC SUMMARY prints nothing, it writes OUTPUT OUT= */
PROC SUMMARY DATA=refadm.adsl NWAY;
  WHERE saffl = "Y";
  CLASS trt01an trt01a;
  VAR age;
  OUTPUT OUT=age_stats(DROP=_type_ _freq_) N=n MEAN=mean STD=sd MEDIAN=median MIN=min MAX=max;
RUN;
TITLE1 "AGE_STATS: one record per treatment";
PROC PRINT DATA=age_stats NOOBS;
  FORMAT mean sd median 5.1;
RUN;

/* BY instead of CLASS: the data must be sorted, and SAS prints one table per group */
PROC SORT DATA=refadm.adsl(WHERE=(saffl = "Y")) OUT=adsl_saf;
  BY trt01an;
RUN;
TITLE1 "Age with BY: one table per treatment";
PROC MEANS DATA=adsl_saf N MEAN MAXDEC=1;
  BY trt01an;
  VAR age;
RUN;

/* a quick look at one variable: the basic measures and the extreme values */
TITLE1 "Baseline weight (kg): a quick look";
ODS SELECT BasicMeasures ExtremeObs;
PROC UNIVARIATE DATA=refadm.adsl NEXTROBS=3;
  WHERE saffl = "Y";
  VAR weightbl;
  ID usubjid;
RUN;
TITLE;

What you should see:

Actual Treatment          N
for Period 01           Obs      N      Mean   Std Dev   Median   Minimum   Maximum
Placebo                  86     86      75.2       8.6     76.0      52.0      89.0
Xanomeline High Dose     84     84      74.4       7.9     76.0      56.0      88.0
Xanomeline Low Dose      84     84      75.7       8.3     77.5      51.0      88.0

Then the data set AGE_STATS, with the same numbers as variables, three small tables made with BY, and the quick look at the baseline weight: mean 66.6 kg, median 66.7 kg, the three lightest subjects (34.0, 39.9 and 41.1 kg) and the three heaviest (102.1, 106.1 and 108.0 kg).

CLASS against BY. CLASS needs no sort and gives one table with a row per group. BY needs data sorted by the BY variables and gives one table per group. Use CLASS for summaries. Use BY when each group needs its own page.
PROC SUMMARY is PROC MEANS without the printed table. OUTPUT OUT= writes the statistics that you name (N=n MEAN=mean ...) as variables. NWAY keeps only the rows for the full CLASS combination. Without NWAY, you get every combination of the CLASS variables: one row for all subjects together (_TYPE_ = 0), rows for each CLASS variable alone, and the full combinations.
PROC UNIVARIATE is the quick look at one variable. The extreme values are the first place where data errors show: a weight of 34 kg, is it real? ID usubjid tells you whom to check.
MAXDEC=1 rounds the printed numbers only. The data set AGE_STATS keeps every decimal.

Exercise 6-3. Remove NWAY from the PROC SUMMARY step. How many records does AGE_STATS have now, and what is the mean age of all 254 subjects?

6.4 The shape of a query

Every query has the same skeleton. The table lists the clauses in a good reading order: from the data to the result. In the program you write them in a fixed order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

Clause	Question it answers
`FROM`	Which table, or which tables joined?
`WHERE`	Which rows go in?
`GROUP BY`	Which rows belong together?
`SELECT`	Which columns and summaries come out?
`HAVING`	Which groups stay?
`ORDER BY`	In which order?

/* Chapter 6: the shape of a query */
PROC SQL;
  SELECT trt01a, COUNT(*) AS n
    FROM refadm.adsl
    WHERE saffl = "Y"
    GROUP BY trt01a;
QUIT;

What you should see: Placebo 86, Xanomeline High Dose 84, Xanomeline Low Dose 84.

PROC SQL ends with QUIT;, not RUN;. Commas separate the items of a list.

Exercise 6-4. Add HAVING COUNT(*) > 85 to 06_skeleton.sas. Which rows stay?

6.5 Big N into macro variables

Every table header shows the number of subjects per treatment: the big N. Compute it once, and keep it in macro variables:

/* Chapter 6: big N per treatment into macro variables */
PROC SQL NOPRINT;
  SELECT trt01an, COUNT(DISTINCT usubjid)
    INTO :trt1-:trt3, :n1-:n3
    FROM refadm.adsl
    WHERE saffl = "Y"
    GROUP BY trt01an;
QUIT;

%PUT NOTE: &=trt1. &=n1. &=trt2. &=n2. &=trt3. &=n3.;

What you should see in the log:

NOTE: TRT1=0 N1=86 TRT2=54 N2=84 TRT3=81 N3=84

INTO :n1-:n3 writes one value per row into n1, n2 and n3.
Select the treatment code too, into trt1-trt3, so that you can prove which count belongs to which arm: 0 is placebo, 54 the low dose and 81 the high dose.
COUNT(DISTINCT usubjid) counts subjects; COUNT(*) counts rows.

6.6 Subjects with at least one TEAE

This is the first row of every safety table: how many subjects had at least one treatment-emergent adverse event (TEAE), as a percent of the big N.

/* Chapter 6: subjects with at least one TEAE, by treatment */
PROC SQL;
  CREATE TABLE teae_n AS
    SELECT a.trt01an, a.trt01a,
           COUNT(DISTINCT a.usubjid)                 AS bign,
           COUNT(DISTINCT b.usubjid)                 AS n_teae,
           CALCULATED n_teae / CALCULATED bign * 100 AS pct FORMAT=5.1
      FROM refadm.adsl(WHERE=(saffl = "Y")) AS a
           LEFT JOIN refadm.adae(WHERE=(trtemfl = "Y")) AS b
           ON a.usubjid = b.usubjid
      GROUP BY a.trt01an, a.trt01a
      ORDER BY a.trt01an;
QUIT;

PROC PRINT DATA=teae_n NOOBS; RUN;

What you should see (press F12 to open TEAE_N):

TRT01AN	TRT01A	bign	n_teae	pct
0	Placebo	86	65	75.6
54	Xanomeline Low Dose	84	77	91.7
81	Xanomeline High Dose	84	76	90.5

The LEFT JOIN keeps every subject of the safety population, also the subjects without an AE. The big N and the count then come from the same query. So the percent cannot use a wrong denominator. With an inner join, the subjects without an AE are lost. Then bign becomes the number of subjects with an AE, and every percent is 100.

CALCULATED lets you use a column that the same SELECT makes.

Exercise 6-5. Change 06_teae.sas to count subjects with at least one serious TEAE (aeser = "Y").

Exercise 6-6. Rewrite 06_teae.sas with an inner join. Predict bign before you run it.

6.7 CASE and the set operators

CASE is the IF-THEN-ELSE of SQL. The set operators combine the rows of two queries: UNION keeps the rows of either, INTERSECT the rows of both, EXCEPT the rows of the first that are not in the second.

/* Chapter 6: CASE, and the set operators UNION, INTERSECT and EXCEPT */
PROC SQL;
  /* CASE: a new column from a rule, as IF-THEN-ELSE in a DATA step */
  CREATE TABLE agecat AS
    SELECT usubjid, age,
           CASE WHEN age < 65  THEN "<65"
                WHEN age <= 80 THEN "65-80"
                ELSE ">80"
           END AS agecat LENGTH=5
      FROM refadm.adsl
      WHERE saffl = "Y";
  TITLE1 "Age groups made with CASE";
  SELECT agecat, COUNT(*) AS n
    FROM agecat
    GROUP BY agecat;

  /* one row per subject in each list */
  CREATE TABLE ae_subj AS SELECT DISTINCT usubjid FROM sdtm.ae;
  CREATE TABLE cm_subj AS SELECT DISTINCT usubjid FROM sdtm.cm;

  TITLE1 "Subjects with an AE, a concomitant medication, or both";
  SELECT "AE or CM (UNION)"       AS question LENGTH=30,
         COUNT(*) AS subjects
    FROM (SELECT usubjid FROM ae_subj UNION SELECT usubjid FROM cm_subj)
  OUTER UNION CORRESPONDING
  SELECT "AE and CM (INTERSECT)"  AS question,
         COUNT(*) AS subjects
    FROM (SELECT usubjid FROM ae_subj INTERSECT SELECT usubjid FROM cm_subj)
  OUTER UNION CORRESPONDING
  SELECT "AE but no CM (EXCEPT)"  AS question,
         COUNT(*) AS subjects
    FROM (SELECT usubjid FROM ae_subj EXCEPT SELECT usubjid FROM cm_subj);
QUIT;
TITLE;

What you should see: the age groups again (65-80 144, <65 33, >80 77, the same as AGEGR1 from ADSL), then:

question                        subjects
AE or CM (UNION)                     244
AE and CM (INTERSECT)                210
AE but no CM (EXCEPT)                 15

225 subjects have an AE (210 + 15) and 229 have a concomitant medication (244 − 15).

CASE tests the WHEN conditions in order and stops at the first true one. Give END AS a name and a LENGTH.
A missing number is smaller than every number. So WHEN age < 65 would also catch a missing age. The pilot has no missing age; your study may have one. Test NOT MISSING(age) first.
The set operators remove duplicate rows. UNION ALL keeps them.
OUTER UNION CORRESPONDING stacks the three one-row queries into one result, by column name.

Exercise 6-7. How many subjects have a concomitant medication but no AE?

6.8 The remerge NOTE

/* EXPECT: NOTE The query requires remerging summary statistics back with the original data */
/* Chapter 6: a summary next to detail rows makes SAS remerge */
PROC SQL;
  CREATE TABLE remerge AS
    SELECT usubjid, age, MEAN(age) AS mean_age
      FROM refadm.adsl;
QUIT;

What you should see:

NOTE: The query requires remerging summary statistics back with the original data.

SAS computed one mean and copied it to all 254 rows. Sometimes you want that. Often it is a missing GROUP BY. A QC log check flags this NOTE, and so does the Suspicious Log. If you want the remerge, compute the summary in a separate step. Then the log stays clean, and the intent is clear.

6.9 Ask SAS about your data: dictionary tables

SAS keeps metadata about every library, data set and variable in dictionary tables. You can query them like any data.

/* Chapter 6: ask SAS about your data */
PROC SQL;
  SELECT name, type, length, label
    FROM dictionary.columns
    WHERE libname = "REFADM" AND memname = "ADSL" AND name LIKE "TRT%";
QUIT;

PROC SQL NOPRINT;
  CREATE TABLE flags AS
    SELECT name, varnum
      FROM dictionary.columns
      WHERE libname = "REFADM" AND memname = "ADSL" AND name LIKE '%FL'
      ORDER BY varnum;
  SELECT name INTO :flvars SEPARATED BY " "
    FROM flags;
QUIT;

%PUT NOTE: &=flvars.;

What you should see: seven variables whose name starts with TRT, among them TRT01P (char, 20, “Planned Treatment for Period 01”) and TRTSDT (num, 8, “Date of First Exposure to Treatment”). Then in the log:

NOTE: FLVARS=SAFFL ITTFL EFFFL COMP8FL COMP16FL COMP24FL DISCONFL DSRAEFL DTHFL

Library and member names are stored in uppercase. So compare them with uppercase values. The first query writes the names to a table. A second query sorts them. A query that sorts by a column that it does not select gives another NOTE that a log check flags.

Exercise 6-8. Put the names of all SDTM data sets into one macro variable &domains. from dictionary.tables.

6.10 The percent sign trap

/* EXPECT: WARNING Apparent invocation of macro FL not resolved */
/* Chapter 6: a percent sign in double quotes calls the macro processor */
PROC SQL NOPRINT;
  SELECT name INTO :flvars SEPARATED BY " "
    FROM dictionary.columns
    WHERE libname = "REFADM" AND memname = "ADSL" AND name LIKE "%FL";
QUIT;

What you should see:

WARNING: Apparent invocation of macro FL not resolved.

Inside double quotes, the macro processor reads %FL as a call of a macro named FL. Single quotes stop the macro processor. That is why 06_dictionary.sas writes '%FL'. Use single quotes for any text with % or & that is not for the macro processor. Part V explains how the macro processor reads your code.

Answers

The answers to Exercises 6-1 to 6-8 are in Answers, Part I.