Count of a specific value within a column 1

[smalek]

Hi
I have a data set which contains Patient IDs and referral reasons Ref1-Ref12. The values which fall under Ref1 - Ref12 are either 1 or 0 (denoting ticked, not ticked). I would like to a breakdown of the referral reasons by the most popular and so on.

Current Data set:
Patient ID Ref1 Ref2 Ref3 Ref4
123564 1 0 1 0

569845 1 0 0 0

785645 1 1 1 0

Desired output:

Ref1 3
Ref3 2
Ref2 1
Ref4 0

In other words I need a count of the '1' which falls under each referral reason's column.

Any suggestions are most welcome.

Thanks

 

[klaz2002]

First you need to count the columns. You can use PROC SQL or any other proc that counts. You must have a group value. The simplest solution is to have all grouped together so set a variable to 1;

data test;
  your data;
  x = 1;
run;
proc sql;

create table test2 as
 select x, sum(ref1) as totalref1, sum(ref2) as totalref2
 from your test
 group by x;
quit;

You will still have to select the most popular, but you get the point.

Klaz

 

[kdt82]

To get the output as you want it, some kind of transpose is required.

This is one of many ways to do it.

  data have ;
input PatientID Ref1 - Ref4 ;
cards;
123564                1       0       1         0
569845                1       0       0         0
785645                1       1       1         0
;
proc transpose data=have out=trans ;
   var ref: ;
   run ;
data want(drop=col:) ;
   set trans ;
   counts = sum(of col:) ;
   run ;
* Optional sort step ;
proc sort data=want ;
   by descending counts ;
   run ;
proc print ;
   run ;

 

[smalek]

Thanks for the postings klaz2002 and kdt82 but I forgot to mention a very important piece of the puzzle. The scores which fall under Ref1-4 can be either 0,1,3, or 9. My final aim is to get a count of only the score=1.

For example
Patient ID Ref1 Ref2 Ref3 Ref4
123564 1 0 1 0

569845 1 0 0 0

785645 1 1 1 0

456789 9 0 3 1

Output
Ref1 3
Ref2 1
Ref3 2
Ref4 1

Any thoughts?
Thanks

 

[kdt82]

Smalek,

Adding an array should do the trick.

data have ;
   input PatientID Ref1-Ref4 ;
   cards ;
123564 1 0 1 0
569845 1 0 0 0
785645 1 1 1 0
456789 9 0 3 1
;
proc transpose data=have out=trans ;
   var ref: ;
   run ;
data want(drop=col:) ;
   set trans ;
   array temp col: ;
   do _n_=1 to dim(temp) ;
      count=sum(count,(temp[_n_]=1)) ;
      end ;
   run ;
proc print; run;

 

[smalek]

Thanks kdt82 for your code. It was more efficient than my long proc means code.