Calcite | Level 5

## counting values in a column and storing in a variable

If I have a dataset as such:

PatID     Num_1  Num_2  Denom

A          0               1          1

B          1               1          1

C          1               1          1

...

And I need to sum up the number of 1's in Num_1, Num_2, and Denom and store them in a variable then further utilize them as such:

Meas1 = Num_1/Denom

Meas2 = Num_2/Denom

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: counting values in a column and storing in a variable

Try the code, thats what I assumed.

5 REPLIES 5
Super User

## Re: counting values in a column and storing in a variable

Do these variable ever take values other than 0, 1 or possibly missing?

If not then

denom=sum(num_1,num_2);

or

denom = sum ( num_1*(num_1=1), num_2*(num_2=1));

or less code though less obvious what it is doing:

denom=count(cats(num_1,num_2),'1');

but is easier to add more variables to

Calcite | Level 5

## Re: counting values in a column and storing in a variable

There are no missing values, only 1's or 0's.  So for num_1 which could have 50,000 variables I want to count how many 1's are in the entire column.  Then count how many 1's are in the num_2 column.  They represent different things so should be stored as separate variables.  The Denom actually has 1 for all the observations since it's a denominator.

Perhaps this example is clearer:

ID     Num_1 Num_2 Denom

A     0          1               1

B     1          1               1

C     1          1               1

D     0          1                1

E     0          0               1

F     0          1               1

So here, Num_1 has two 1's in the column.  Num_2 has five 1's in the column.  Denom has six 1's.

For Meas1 representing Num_1, it would be 2/6.  For Meas2 representing Num_2, it would be 5/6.

Super User

## Re: counting values in a column and storing in a variable

I assume you mean over rows, not columns.

Proc means works well:

proc means data=have sum;

var meas1 meas2 denom;

output out=want sum=/autoname;

run;

Proc print data=want;

run;

Calcite | Level 5

## Re: counting values in a column and storing in a variable

For the purpose of what I need to do, it's the 1's in all of num_1 taken as a total sum (say 2 in my example) divided by the total sum of all the 1's in the denom column (3 in my example).  Same for num_2.  I wasn't clear sorry, didn't mean it to be taken as num_1/denom and num_2/denom per row but counted through the entire column of x observations.

Super User

## Re: counting values in a column and storing in a variable

Try the code, thats what I assumed.

Discussion stats
• 5 replies
• 869 views
• 0 likes
• 3 in conversation