topic Re: Generate max count variable, across observations in SAS Procedures

Generate max count variable, across observations

awc380 — Thu, 26 Mar 2009 17:31:40 GMT

Hi!

I'm trying to generate a specific 'count' variable (I know...aren't we all), and I know how to do basic count variable (as below), but I want the variable 's_count' to equal the total count for each observation, generated by 'id'.

I can get part of the way there, but I need the 's_count' variable to have no missing observations. i.e.: in the chart below, 's_count' SHOULD read (1,2,2,3,3,3) - I need 's_count' to show the number of observations having the same id number. (A sample fraction, of sorts.)

num id count s_count

1 a 1 1
2 b 1 .
3 b 2 2
4 c 1 .
5 c 2 .
6 c 3 3

This was the best I could do. Here was my data step:

data ds2;
set ds1;
count + 1;
by id;
if first.id then count = 1;
if last.id then s_count = count;
run;

Let me know if I'm being entirely unclear...

Re: Generate max count variable, across observations

deleted_user — Thu, 26 Mar 2009 17:47:56 GMT

Many ways to do this. Simplest would be:

proc sql;
create table tot_count as
select *, max(count) as s_count
from ds2
group by id;
quit;

ie dont do the s_count in the datastep.

even easier:

proc sql;
create table ds2 as
select *, count(*) as s_count
from ds1
group by id;
quit;

from the basic data.

Re: Generate max count variable, across observations

awc380 — Fri, 27 Mar 2009 14:30:44 GMT

Great! Worked perfectly.
Thanks a lot.

Andy