Hello, Below is a pure PROC SQL solution to your question. The output would look like this: As others have alluded to, there are most certainly many other ways to tackle this problem. That being said, these series of SQL statements run efficiently ... potentially more efficiently than using several separate PROC FREQ statements that generate datasets using the OUT option (for which the output would still have to be reassembled). PROC SQL; CREATE TABLE example_data ( group VARCHAR(10) , gender VARCHAR(1) , enrolled VARCHAR(1) , finished VARCHAR(1) ); QUIT; /* same as your data */ PROC SQL; INSERT INTO example_data values ('group 1', 'M', 'Y', 'Y'); INSERT INTO example_data values ('group 1', 'M', 'Y', 'Y'); INSERT INTO example_data values ('group 1', 'F', 'Y', 'Y'); INSERT INTO example_data values ('group 1', 'F', 'Y', 'N'); INSERT INTO example_data values ('group 2', 'M', 'Y', 'Y'); INSERT INTO example_data values ('group 2', 'M', 'Y', 'Y'); INSERT INTO example_data values ('group 2', 'M', 'Y', 'Y'); INSERT INTO example_data values ('group 2', 'F', 'Y', 'N'); INSERT INTO example_data values ('group 1', 'F', 'Y', 'N'); INSERT INTO example_data values ('group 1', 'F', 'Y', 'Y'); INSERT INTO example_data values ('group 2', 'M', 'Y', 'Y'); INSERT INTO example_data values ('group 2', 'F', 'Y', 'Y'); QUIT; /* create one record table similar to Oracle's SYS.DUAL table */ /* this handy technique allows us to select macro variables within a SQL statement out of thin air */ DATA dummy_row; dummy = 'X'; RUN; /* set up macro variables for later use */ PROC SQL; SELECT SUM(CASE WHEN enrolled = 'Y' AND group = 'group 1' THEN 1 ELSE 0 END) AS G1 , SUM(CASE WHEN enrolled = 'Y' AND group = 'group 2' THEN 1 ELSE 0 END) AS G2 INTO :enrolled_g1, :enrolled_g2 FROM example_data; SELECT SUM(CASE WHEN finished = 'Y' AND group = 'group 1' THEN 1 ELSE 0 END) AS G1 , SUM(CASE WHEN finished = 'Y' AND group = 'group 2' THEN 1 ELSE 0 END) AS G2 INTO :finished_g1, :finished_g2 FROM example_data; QUIT; /* use "UNION ALL" because using "UNION" will impact sort position */ PROC SQL; CREATE TABLE example_summary AS SELECT "enrolled" length=20 AS stat_label , &enrolled_g1 AS G1 , . format=percent8.2 AS G1_ratio , &enrolled_g2 AS G2 , . format=percent8.2 AS G2_ratio FROM dummy_row UNION ALL SELECT "finished" AS stat_label , &finished_g1 AS G1 , (&finished_g1/&enrolled_g1) AS G1_ratio , &finished_g2 AS G2 , (&finished_g2/&enrolled_g2) AS G2_ratio FROM dummy_row UNION ALL SELECT 'M' AS stat_label , SUM(CASE WHEN gender = 'M' AND group = 'group 1' THEN 1 ELSE 0 END) AS G1 , (CALCULATED G1/&finished_g1) AS G1_ratio , SUM(CASE WHEN gender = 'M' AND group = 'group 2' THEN 1 ELSE 0 END) AS G2 , (CALCULATED G2/&finished_g2) AS G2_ratio FROM example_data UNION ALL SELECT 'F' AS stat_label , SUM(CASE WHEN gender = 'F' AND group = 'group 1' THEN 1 ELSE 0 END) AS G1 , (CALCULATED G1/&finished_g1) AS G1_ratio , SUM(CASE WHEN gender = 'F' AND group = 'group 2' THEN 1 ELSE 0 END) AS G2 , (CALCULATED G2/&finished_g2) AS G2_ratio FROM example_data; QUIT;
... View more