topic Re: How to count in multiple columns with Proc Freq in SAS Enterprise Guide

How to count in multiple columns with Proc Freq

muchkin — Tue, 16 Jun 2015 02:17:31 GMT

Hi, I am using SAS EG 5.1 at work. I have a data base like below, with 14 columns. 1st column is unique IDs , and the rest are Instructions, which are in form of codes (numbers between 0 - 200) . Is there a way I can calculate the frequency of occurrence of a specific code value (for eg 30) in the whole instruction 1 : instruction 13 grid collectively. I have tried Proc freq, but that gives me tables for single columns separately.

I have previously done this in excel using the countif (range, criteria) code where I have set the range to the whole grid, and criteria as the value of the specific code.

Is there a SAS EG/BASE equivalent to the above function , where I can find the collective frequency of a specific number in a range of columns.

instruction 1

instruction2

instruction 3

instruction 4

instruction 10

instruction 11

instruction 12

instruction 13

Thank you for the help

Re: How to count in multiple columns with Proc Freq

TomKari — Tue, 16 Jun 2015 12:22:40 GMT

Yes, and you can even do it in the tasks in Enterprise Guide, without writing code.

1. Use the Transpose Data task to turn your "instruction" columns into a single row. Put ID into the "group analysis by" role (you'll need to have sorted by ID if they're not in that sequence), and the "instruction" columns into the "Transpose variables" role. You'll see the result will be a three-column table with your ID, the name of the column that contributed the value, and the value.

2. As an optional step, if you only want to analyse particular "instruction" columns, run a query to exclude the rows that you don't want. (Alternatively, you could get rid of the columns prior to the transpose).

3. You should be able to use several of the "Describe" tools to obtain frequency counts on the data values.

Tom

Re: How to count in multiple columns with Proc Freq

Steelers_In_DC — Tue, 16 Jun 2015 12:30:05 GMT

Here you go, I shortened my answer but I think you'll get the idea:

data have ;

input id instruction1 instruction2 instruction3 instruction4 instruction5 instruction6;

cards;

1 1 0 0 0 0 0

2 0 0 0 0 0 0

3 1 1 1 1 1 1

4 0 0 1 0 1 0

5 1 0 1 0 1 0

;

proc transpose data=have out=prep;by id;

proc sql;

create table want as

select count(col1) as result

from prep

where col1 = 1;