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?
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;
That was cool - worked perfect - thanks!
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;
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.