Hello
Let's sat that I have a row data set that contain for each ID ,multiple categorical binary variables: Z1,Z2,Z3..........Z10.
I want to create a summary table with two columns:
Columns 1 will be called "Field" and will get value of the binary var name
Columns 1 will be called "Count" and will get value of number of rows with value1
The wanted data set will look like that:
What is the best way to do it please?
Hi @Ronein
Here is an attempt to do this.
Let me know if that does answer your question.
Best,
data have;
input ID Z1-Z10;
datalines;
1 1 0 1 0 1 0 1 0 1 0
2 0 0 0 0 0 0 0 0 0 0
3 1 1 1 1 1 1 1 1 1 1
4 1 1 1 1 1 0 0 0 0 0
;
run;
data have_sum;
set have end=_end;
array z(10);
array count(10) _temporary_;
do i=1 to dim(z);
count(i)+ z(i);
z(i) = count(i);
end;
drop i id;
if _end then output;
run;
proc transpose data=have_sum out=want (rename= (col1=Count)) name = Field;
var Z1-Z10;
run;
Some explicit example might help.
If this is a report that people will read and not an odd data set structure and your binary variables are coded 1/0 then the summary statistic SUM will get you the number of 1 values.
So something like:
Proc tabulate data=have;
var z1-z10;
table z1-z10,
sum='Count of 1'
;
run;
may work.
BTW: The Mean statistic will give you the percent of 1's in the data in decimal form for 1/0 coded values.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.