Desktop productivity for business analysts and programmers

How to count in multiple columns with Proc Freq

Reply
New Contributor
Posts: 3

How to count in multiple columns with Proc Freq

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.

IDinstruction 1instruction2instruction 3instruction 4instruction 10
instruction 11
instruction 12
instruction 13

Thank you for the help Smiley Happy

Trusted Advisor
Posts: 1,050

Re: How to count in multiple columns with Proc Freq

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

Valued Guide
Posts: 854

Re: How to count in multiple columns with Proc Freq

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;

Ask a Question
Discussion stats
  • 2 replies
  • 547 views
  • 3 likes
  • 3 in conversation