How to create a dataset with count N of two columns with specific conditions

lkhadr — Thu, 10 Feb 2022 16:06:17 GMT

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!

Re: How to create a dataset with count N of two columns with specific conditions

ChrisNZ — Thu, 17 Feb 2022 04:55:20 GMT

proc freq allows you to create a data set. Look at the output statement;

Re: How to create a dataset with count N of two columns with specific conditions

Tom — Thu, 17 Feb 2022 05:34:38 GMT

So use PROC FREQ.

PROC FREQ DATA=have;
   BY POLICE_DEPT; 
   TABLES ATT_PRE*ATT_POST / noprint out=want ;
RUN;

topic How to create a dataset with count N of two columns with specific conditions in SAS Programming

How to create a dataset with count N of two columns with specific conditions

Re: How to create a dataset with count N of two columns with specific conditions

Re: How to create a dataset with count N of two columns with specific conditions