Hello!
I am trying to make a dataset that has an column N based on the values of two other variables grouped by the category "police department".
More specifically, I have a dataset with individual ID, police department the individual(s) work at (there are 5 total in the dataset), one variable that asks if they agree or disagree with a statement regarding drug addition before a training (1=Strongly Disagree, 2=Disagree, 3=Neutral, 4=Agree, 5=Strongly Agree), and then another variable that asks the same question regarding drug addiction AFTER training. I ultimately want to create a dataset that shows how many police officers by police department said "strongly disagree" before and then "agree" after, "strongly disagree" before and then "strongly agree" after, etc. basically every combination of the 5 possible responses before versus 5 possible responses after.
My dataset looks like this:
ID | POLICE_DEPT | ATT_PRE | ATT_POST |
1 | A | 1 | 2 |
2 | A | 2 | 3 |
3 | A | 2 | 3 |
4 | A | 5 | 1 |
5 | A | 5 | 1 |
6 | B | 3 | 3 |
7 | B | 3 | 3 |
8 | B | 3 | 3 |
9 | B | 2 | 1 |
10 | B | 2 | 1 |
11 | C | 4 | 4 |
12 | C | 4 | 4 |
But I want this:
POLICE_DEPT | ATT_PRE | ATT_POST | COUNT |
A | 1 | 2 | 1 |
A | 2 | 3 | 2 |
A | 5 | 1 | 2 |
B | 3 | 3 | 3 |
B | 2 | 1 | 2 |
C | 4 | 4 | 1 |
I realize PROC FREQ DATA=have; TABLE ATT_PRE*ATT_POST BY POLICE_DEPT; RUN would get me this data, but I want it in dataset form so I can use it to make an alluvial plot.
I tried playing around with PROC SQL queries but they didn't work like this one:
PROC SQL;
CREATE TABLE want AS
SELECT police_dept, att_pre, att_post, count(police_dept)
FROM have
GROUP BY police_dept;
QUIT;
Thank you!
proc freq allows you to create a data set. Look at the output statement;
So use PROC FREQ.
PROC FREQ DATA=have;
BY POLICE_DEPT;
TABLES ATT_PRE*ATT_POST / noprint out=want ;
RUN;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.