topic Re: frequency count across variables in SAS Procedures

frequency count across variables

Angi — Thu, 26 Feb 2015 09:48:57 GMT

I have a dataset that contains policy violations by students. The type of violation is coded from 1 to 40. Some students have only one violation while others have up to 13 violations.

I am trying to get a frequency count across variables for the different types of violations that have occurred.

Dataset Have

id	violation1	violation2	violation3	violation4	violation5
A1	1	2	6
A2	5	3	1	1	3
A3	7	1	4	1	5
A4	2	8	1	2
A5	1	7	2	8	6

Output Want

Violation_type	frequency
1	7
2	4
3	2
4	1
5	2
6	2
7	2
8	2

Thanks in advance!

Re: frequency count across variables

RW9 — Thu, 26 Feb 2015 10:11:55 GMT

Hi,

Well, several ways of doing this. You could for instance have an array of vio1-vio7 and set them to 1 where they exist and freq that. My preferred method would be to normalise (vertical) the data, then do a simple count on that:

data have;

id="A1"; violation1=1; violation2=2; violation3=6; output;

id="A2"; violation1=5; violation2=3; violation3=1; output;

id="A3"; violation1=7; violation2=1; violation3=4; output;

run;

proc transpose data=have out=inter;

by id;

var violation1-violation3;

run;

proc sql;

create table WANT as

select COL1 as VIOLATION_TYPE,

COUNT(COL1) as FREQUENCY

from WORK.INTER

group by COL1;

quit;

Re: frequency count across variables

Kurt_Bremser — Thu, 26 Feb 2015 10:26:27 GMT

A different approach, verticalizing the data in a data step and using freq for the count:

data have;

infile cards;

input

id :$2.

violation1

violation2

violation3

violation4

violation5

;

cards;

A1 1 2 6 . .

A2 5 3 1 1 3

A3 7 1 4 1 5

A4 2 8 1 2 .

A5 1 7 2 8 6

;

run;

data int (keep=violation_type);

set have;

array viols {*} _numeric_;

do i = 1 to dim(viols);

if viols{i} ne .

then do;

violation_type = viols{i};

output;

end;

run;

proc freq data=int noprint;

tables violation_type /out=want(drop=percent);

run;

Re: frequency count across variables

Haikuo — Thu, 26 Feb 2015 13:53:21 GMT

As for a one-step approach, Hash can be an option: (Raw data stole from Kurt)

data have;

infile cards;

input

id :$2.

violation1

violation2

violation3

violation4

violation5

;

cards;

A1 1 2 6 . .

A2 5 3 1 1 3

A3 7 1 4 1 5

A4 2 8 1 2 .

A5 1 7 2 8 6

;

run;

data _null_;

if _n_=1 then

do;

dcl hash h(ordered:'a');

h.definekey('vtype');

h.definedata('vtype', 'freq');

h.definedone();

call missing (vtype, freq);

end;

set have end=last;

array v violation:;

do over v;

if not missing(v) then

do;

vtype=v;

if h.find() ne 0 then

do;

freq=1;

rc=h.replace();

end;

else

do;

freq+1;

rc=h.replace();

end;

if last then

rc=h.output(dataset:'want');

run;

Re: frequency count across variables

Angi — Fri, 27 Feb 2015 01:33:40 GMT

Thank you RW9, Kurt, and Hai!