topic Re: How to record a max count in SAS Programming

How to record a max count

SL_MN — Wed, 11 Jun 2014 19:47:52 GMT

I have the following data, but 1500 lines of it with 500 + different locals.

ID Location count

20030045 19355 1

8015393427 19355 2

20030044 19355 3

20030060 19355 4

20030038 19355 5

4510380 20002 1

3000089236 20004 1

239167 20740 1

9508038115 20740 2

678827 20770 1

1600001004 20770 2

I need to add a colum that shows the total number in each location like this:

ID Local count ttl at local

20030045 19355 1 5

8015393427 19355 2 5

20030044 19355 3 5

20030060 19355 4 5

20030038 19355 5 5

4510380 20002 1 1

3000089236 20004 1 1

239167 20740 1 2

9508038115 20740 2 2

678827 20770 1 2

1600001004 20770 2 2

Does anyone have a suggestion?

Re: How to record a max count

stat_sas — Wed, 11 Jun 2014 20:11:09 GMT

proc sql;

select a.*,b.ttl_location from have a

inner join (select location,count(location) as ttl_location from have group by location) b

on a.location=b.location;

quit;

Re: How to record a max count

SL_MN — Wed, 11 Jun 2014 20:12:30 GMT

I should add, I tried seperating out the lines with the max total and the location and joining back to the original but that was no good. Code looked like this:

data e1 (drop=count);
set e;
by Location;
if first.Location then count=1;
do ct = count;
end;
count+1;
If last.Location then x = ct;
run;

proc sql;
create table forjoin as
select Location, ct
from e1
where x <> .;
run;

proc sql;
create table e2 as
select * from e1
left join forjoin on
e1.location = forjoin.location;
quit;

Re: How to record a max count

SL_MN — Wed, 11 Jun 2014 20:23:15 GMT

That was cool - worked perfect - thanks!

Re: How to record a max count

stat_sas — Wed, 11 Jun 2014 20:26:55 GMT

No problem - just added order by cluase so that you can get the exact desired output.

proc sql;

select a.*,b.ttl_location from have a

inner join (select location,count(location) as ttl_location from have group by location) b

on a.location=b.location

order by location,count;

quit;

Re: How to record a max count

Haikuo — Wed, 11 Jun 2014 20:28:37 GMT

Should be simple Proc SQL task, no joins needed.

PROC SQL;

CREATE TABLE WANT AS

SELECT *, COUNT(*) /*MAX(COUNT)*/

AS TOTAL FROM HAVE GROUP BY LOCATION;

QUIT;

Regards,

Haikuo