DATA Step, Macro, Functions and more

How to record a max count

Reply
New Contributor
Posts: 3

How to record a max count

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?

Trusted Advisor
Posts: 1,230

Re: How to record a max count

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;

New Contributor
Posts: 3

Re: How to record a max count

That was cool - worked perfect - thanks! 

Trusted Advisor
Posts: 1,230

Re: How to record a max count

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;

New Contributor
Posts: 3

Re: How to record a max count

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;

Respected Advisor
Posts: 3,156

Re: How to record a max count

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

Ask a Question
Discussion stats
  • 5 replies
  • 305 views
  • 0 likes
  • 3 in conversation