topic Re: counting values in a column and storing in a variable in SAS Procedures

counting values in a column and storing in a variable

pinkyc — Wed, 11 Feb 2015 21:18:14 GMT

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

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

ballardw — Wed, 11 Feb 2015 21:27:55 GMT

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

If not then

denom=sum(num_1,num_2);

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

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

Reeza — Wed, 11 Feb 2015 21:39:50 GMT

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;

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

pinkyc — Wed, 11 Feb 2015 21:46:28 GMT

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.

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

pinkyc — Wed, 11 Feb 2015 21:51:58 GMT

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.

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

Reeza — Wed, 11 Feb 2015 22:23:18 GMT

Try the code, thats what I assumed.