turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- counting values in a column and storing in a varia...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-11-2015 04:18 PM

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

Accepted Solutions

Solution

02-11-2015
05:23 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to pinkyc

02-11-2015 05:23 PM

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to pinkyc

02-11-2015 04:27 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

02-11-2015 04:51 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to pinkyc

02-11-2015 04:39 PM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

02-11-2015 04:46 PM

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.

Solution

02-11-2015
05:23 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to pinkyc

02-11-2015 05:23 PM

Try the code, thats what I assumed.