Create count variable by ID

[Mikkel_madsen]

Hi, 

I have the dataset "have" where I want to create a new variable. 

The new variable should count observations with the same ID. I have:

ID	GPS	Open	Store
1	89503	Y	1
1	12353	Y	1
1	06834	N	2
2	95736	N	2
2	01475	Y	3
3	64758	Y	4

The new variable count should be like:

ID	GPS	Open	Store	Count
1	89503	Y	1	3
1	12353	Y	1	3
1	06834	N	2	3
2	95736	N	2	2
2	01475	Y	3	2
3	64758	Y	4	1

Hope you can help me! Thank you. 

Kind regards MM

[novinosrin · Accepted solution]

data have;
input ID	GPS $	Open $	Store;
cards;
1	89503	Y	1
1	12353	Y	1
1	06834	N	2
2	95736	N	2
2	01475	Y	3
3	64758	Y	4
;

proc sql;
create table want as
select *, count(id) as count
from have
group by id
order by id,store;
quit ;

[ed_sas_member]

Hi @Mikkel_madsen 

You can use a PROC SQL for example:

proc sql;
	create table want as
	select *, count(*) as count
	from have
	group by id;
run;

Best,

[novinosrin]

Hi @Mikkel_madsen  I am sharing another smart stuff taught by genius @mkeintz  to whom I owe a lot of my learning

data have;
input ID	GPS $	Open $	Store;
cards;
1	89503	Y	1
1	12353	Y	1
1	06834	N	2
2	95736	N	2
2	01475	Y	3
3	64758	Y	4
;

data want;
 set have(in=a) have(in=b);
 by id;
 retain count;
 if first.id then count=0;
 if a then count=sum(count,1);
 if b;
run;

[mkeintz]

And, in the pursuit of terminal cuteness, one can: 

data have;
input ID	GPS $	Open $	Store;
cards;
1	89503	Y	1
1	12353	Y	1
1	06834	N	2
2	95736	N	2
2	01475	Y	3
3	64758	Y	4
;

data want;
 set have(in=countme) have(in=keepme);
 by id;
 if first.id then count=0;
 count+countme;
 if keepme;
run;

Sorry ... couldn't resist.