06-15-2015 10:17 PM
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.
|ID||instruction 1||instruction2||instruction 3||instruction 4||instruction 10|
Thank you for the help
06-16-2015 08:22 AM
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.
06-16-2015 08:30 AM
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;
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;
create table want as
select count(col1) as result
where col1 = 1;