BookmarkSubscribeRSS Feed
SL_MN
Calcite | Level 5

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?

5 REPLIES 5
stat_sas
Ammonite | Level 13

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;

SL_MN
Calcite | Level 5

That was cool - worked perfect - thanks! 

stat_sas
Ammonite | Level 13

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;

SL_MN
Calcite | Level 5

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;

Haikuo
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1267 views
  • 0 likes
  • 3 in conversation